Search For
Flagging a Snippet, takes the snippet to Moderator Attention;


0
The first day of the current month can be computed as,
SELECT ADD_MONTHS(input_date - EXTRACT(DAY FROM input_date)+1, 0)


How it works ?

First we extract the day from the date and subtracting from the date itself.
So that we are getting the last date of the last month.

input_date - EXTRACT(DAY FROM input_date) ---> last month's end date.

After that we add '1' to that, so the first date of the present month will be the result.

Please note that whenever we compute the first and last date(s) of a month, it is better to proceed from the start date of the month. (Because, the last date will vary for months i.e. Feb-28, Oct- 31, Nov - 30)

Now we have got the start date of the current month and we are using ADD_MONTHS just to get the date output in proper format.

Example:

Example:
Consider, date_1 table is having data like below

input_date
12/30/2012
02/28/2010
01/12/2011
07/12/2012

SELECT 
input_date,
ADD_MONTHS(input_date - EXTRACT(DAY FROM input_date)+1, 0) AS current_start
FROM date_1


Output:
input_datecurrent_start
12/30/201212/01/2012
02/28/201002/01/2010
01/12/201101/01/2011
07/12/201207/01/2012
Vote! You feel this code is working like charm ? Go a head Login and Press up arrow.
Didn't work ?, press down arrow and let us know the error in comments

Comments