SQL 1st Day of Month
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.
Subscribe via RSS