# Date Calculations - First of Month, Last of Month, More

 0 This is how I compute a few useful dates in SQL Server: `````` declare @today datetime /* todays date only, no time */ declare @yesterday datetime /* yesterdays date */ declare @fdcm datetime /* first day, current month */ declare @ldcm datetime /* last day, current month */ declare @fdnm datetime /* first day, next month */ declare @ldpm datetime /* last day, previous month */ declare @fdpm datetime /* first day, previous month */ declare @ldppm datetime /* last day, month previous to previous */ set @today = convert(datetime,convert(varchar(11),getdate())) /* strip to date only */ -- set @today = convert(datetime,'2008-02-15') set @fdcm = dateadd(dd,-(datepart(dd,@today)-1),@today) set @fdnm = dateadd(mm,1,@fdcm) set @ldcm = dateadd(dd,-1,@fdnm) set @yesterday = dateadd(dd,-1,@today) set @ldpm = dateadd(dd,-datepart(dd,@today),@today) set @fdpm = dateadd(dd,-(datepart(dd,@ldpm))+1,@ldpm) set @ldppm = dateadd(dd,-datepart(dd,@ldpm),@ldpm) select @today as today select @fdcm as first_day_current_month select @fdnm as first_day_next_month select @ldcm as last_day_current_month select @yesterday as yesterday select @ldpm as last_day_previous_month select @fdpm as first_day_previous_month select @ldppm as last_day_pre_previous_month `````` So, you can see we have several different, useful dates loaded into variables. Even if I just need @fdcm (first day, current month), I usually just toss the entire block of code into my proc. I'm going to want to do similar date calcs in Oracle, too. If you've got similar date calcs in Oracle, please post them, as I'd like to to learn how Oracle developers typically address similar problems. And if you're an Oracle developer searching for a way to do similar date calcs in SQL Server, well, here you go.If you want to see date examples other than today, just uncomment the second set of @today and fill in any date you like. more ▼ asked Feb 10, 2010 at 01:26 PM in Default KillerDBA 1.5k ● 8 ● 9 ● 10 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 Thanks, that really gives me a chance to demonstrate why I hate T-SQL date handling. The equivalent in PL/SQL is: ``````DECLARE today date; -- today's date yesterday date; -- yesterday's date fdcm date; -- first day current month ldcm date; -- last day, current month fdnm date; -- first day, next month ldpm date; -- last day, previous month fdpm date; -- first day, previous month ldppm date; -- last day, previous previous month BEGIN today := TRUNC(SYSDATE); yesterday := today - 1; fdcm := TRUNC(today,'MONTH'); ldcm := LAST_DAY(today); fdnm := ldcm + 1; -- could also get it directly as TRUNC(ADD_MONTHS(sysdate,1),'MONTH') ldpm := fdcm - 1; -- could also get it directly as LAST_DAY(ADD_MONTHS(sysdate,-1)) fdpm := TRUNC(ldpm,'MONTH'); -- could also get it directly as TRUNC(ADD_MONTHS(sysdate,-1),'MONTH') ldppm := fdpm - 1; -- could also get it directly as LAST_DAY(ADD_MONTHS(sysdate,-2)) END; ``````In practice, I don't usually end up using variables like these in Oracle, since the date handling is so simple to use (at least once you've got it by heart that there are 1440 minutes, or 86400 seconds, in a day, so expressions like sysdate+15/1440 are common). more ▼ answered Feb 10, 2010 at 08:00 PM Andrew Mobbs 1.5k ● 1 ● 3 "Thanks, that really gives me a chance to demonstrate why I hate T-SQL date handling" - Glad I was able to add something to your day. The TRUNC function is more useful than I realized. Thanks. Feb 12, 2010 at 11:42 AM KillerDBA On reflection, while The Oracle Way is reasonably easy, I find it dissatisfying. I'm a big fan of rich datatypes and datetime/date/time/interval are superior ways do deal with date calculations. This isn't intended as praise for SQL Server, mind you, but I personally think it's advantageous to be forced to think about date calculations differently. I'm not practiced with the interval type described in the Standard but I think I'd like it. Feb 15, 2010 at 12:01 AM KillerDBA add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 To get you started -` SELECT SYSDATE FROM DUAL - today date SELECT SYSTIMESTAMP FROM DUAL - today timestamp SELECT SYSDATE - 1 FROM DUAL - Yesterday ` more ▼ answered Feb 10, 2010 at 03:22 PM HillbillyToad 1k ● 2 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

### New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

By Email:

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x378
x50
x28

asked: Feb 10, 2010 at 01:26 PM

Seen: 6245 times

Last Updated: Sep 09, 2012 at 03:45 AM