MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

SQL 1st Day of Month

with 6 comments

A couple years ago I showed how to get the last day of the month and count past leap year. Somebody asked how to get the first day of the current month.

The two easiest ways I know how to do that in Oracle is to combine the ADD_MONTHS and LAST_DAY functions, like:

SELECT
  TO_CHAR(TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1),'DD-MON-YY HH24:MI') AS "One Way"
, TO_CHAR(TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-1)),'DD-MON-YY HH24:MI') AS "Another Way"
FROM  dual

It gives you a truncated datetime value, as you can see:

One Way         Another Way
--------------- ---------------
01-JUN-11 00:00 01-JUN-11 00:00

As the comments point out, the following is best for getting the first day of the month:

SELECT TO_CHAR(TRUNC(SYSDATE,'MM'),'DD-MON-YYYY HH24:MI') FROM dual;

You can also return the first day of the year like this:

SELECT TO_CHAR(TRUNC(SYSDATE,'YY'),'DD-MON-YYYY HH24:MI') FROM dual;

If you’re planning to do this in an application and Oracle Database 11g, you may want to write it as a deterministic function, like this:

1
2
3
4
5
6
CREATE OR REPLACE FUNCTION first_day
( date_in DATE ) RETURN DATE DETERMINISTIC IS
BEGIN
  RETURN TRUNC(date_in,'MM');
END;
/

Naturally, you can also do this with the EXTRACT function but it would get very complex quickly. Hope this answers the question.

Written by maclochlainn

June 7th, 2011 at 11:45 pm

Posted in Oracle,sql

6 Responses to 'SQL 1st Day of Month'

Subscribe to comments with RSS or TrackBack to 'SQL 1st Day of Month'.

  1. Wouldnt TRUNC work?

    TRUNC(SYSDATE, ‘MON’)

    Stephen Bealer

    8 Jun 11 at 1:04 am

  2. Isn’t this easier?

    SELECT TRUNC(SYSDATE, ‘mm’) FROM dual;

    Thanks,
    Paolo

    PaoloM

    8 Jun 11 at 1:35 am

  3. You can specify the unit to which TRUNC truncate dates.

    SELECT
    TO_CHAR(TRUNC(SYSDATE,’MM’),’DD-MON-YY HH24:MI’) AS “Another Way”
    FROM dual

    Jens

    8 Jun 11 at 2:39 am

  4. Hi Michael,

    another way is to use the TRUNC function with the ‘MM’ parameter, i.e.

    SQL> SELECT trunc(sysdate, 'MM') FROM dual;
     
    TRUNC(SYSDATE,'MM')
    --------------------
    01-JUN-2011 00:00:00

    Chadders

    8 Jun 11 at 2:56 am

  5. Quicker : TRUNC(SYSDATE,’MM’)
    You can do it with years and quarters too.

    Gary

    8 Jun 11 at 3:55 am

  6. The easiest way is trunc(in_date,’MONTH’)

    Alistair Wall

    8 Jun 11 at 4:16 am

Leave a Reply