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

Question

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.

Answer 1

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

Answer 2

0

To get you started -

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

answered Feb 10 '10 at 03:22 PM

HillbillyToad
1k ● 2

(comments are locked)

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

Follow this question

Related Questions