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

Question

question

KillerDBA asked Feb 10, '10

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.

oracle plsql date

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-02-10T20:00:48Z

Andrew Mobbs answered Feb 10, '10

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).

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KillerDBA commented · Feb 12, 2010 at 11:42 AM

"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.

0 ·

KillerDBA commented · Feb 15, 2010 at 12:01 AM

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.

0 ·

Answer 2 · 2010-02-10T15:22:23Z

HillbillyToad answered Feb 10, '10

To get you started -

 SELECT SYSDATE FROM DUAL - today date
 SELECT SYSTIMESTAMP FROM DUAL - today timestamp
 SELECT SYSDATE - 1 FROM DUAL - Yesterday

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question