x

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

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 gravatar image

KillerDBA
1.5k 8 9 10

(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

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 gravatar image

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
(comments are locked)
10|1200 characters needed characters left

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 gravatar image

HillbillyToad
1k 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

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.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x378
x50
x28

asked: Feb 10, 2010 at 01:26 PM

Seen: 6070 times

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