CODEWITHSUNDEEP
sqlsql-serverdate
Jean
Jean

Reputation: 57

SQL Finding the first day of the month of a data set

I've found lots of code in getting the sql to display the first of a month, but I need to display the first day of the month based on my data set not just [month] 1st [year]

EX 1: January 1st is a holiday, so it'll never be the first day of the month in the data set, the first day of January is January 2nd. Another example is if the first date of the month is the 7th in my data set, I want to see the 7th not the 1st.

This is my data set

DATE
----------
2016-02-01
2016-02-05
2016-02-08
2016-02-19
2016-02-20
2016-02-22
2016-05-02
2016-05-05
2016-05-07
2016-05-09
2016-05-11
2016-05-23
2016-06-01    
2016-06-10
2016-06-20
2016-06-29
2016-07-01   
2016-07-07 
2016-07-14 
2016-07-21 
2016-07-28 
2016-07-31 
2016-08-04 
2016-08-10 
2016-08-18 
2017-02-23 
2017-02-30

I need this to display

DATE
----------
2016-02-01
2016-05-02
2016-06-01    
2016-07-01   
2016-08-04 
2017-02-23

I keep getting stuck, I thought this may work but I'm not getting the min date for that month

select min(load_date) from multi_dt
group by month(load_date)

Upvotes: 0

Views: 607

Answers (3)

Steve Lovell
Steve Lovell

Reputation: 2564

Your initial answer was fine, you just also needed to group by the year.

group by
month(load_date),year(load_date)

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

Try this:

select min(load_date) as min_load_date
from multi_dt
group by  dateadd(month, datediff(month, 0, load_date )  , 0)

Using month() only returns the month, but using the function in the query above will return the first of the month, but as a datetime datatype so when you group by it, it is including the year and the month.

rextester demo: http://rextester.com/UJRN68337

returns:

+---------------+
| min_load_date |
+---------------+
| 2016-02-01    |
| 2016-05-02    |
| 2016-06-01    |
| 2016-07-01    |
| 2016-08-04    |
| 2017-02-23    |
+---------------+

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I would use row_number():

select t.date
from (select t.*,
             row_number() over (partition by year(date), month(date) order by date) as seqnum
      from t
     ) t
where seqnum = 1;

If you don't need any additional columns, an aggregation would be equivalent:

select min(t.date)
from t
group by year(t.date), month(t.date);

Upvotes: 0

Related Questions