CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Administrateur
Administrateur

Reputation: 901

Get start and end of each month between two dates

I have this table:

create table #tbl
 (
    dt datetime
 )

 insert into #tbl values ('2013-01-01 00:00:00')
 insert into #tbl values ('2013-02-01 00:00:00')
 insert into #tbl values ('2013-02-02 00:00:00')
 insert into #tbl values ('2013-03-01 00:00:00')

I need to get the start and end of each distinct month, in other words, this is the expected result:

[start]                    [end]
 2013-01-01 00:00:00.000    2013-01-31 23:59:59.997
 2013-02-01 00:00:00.000    2013-02-28 23:59:59.997
 2013-03-01 00:00:00.000    2013-03-31 23:59:59.997

I'm not sure how to do it. Plz help.

select
 dateadd(mm, datediff(mm, 0, ???, 0),
 dateadd(ms, -3, dateadd(mm, datediff(m, 0, ??? + 1, 0))

I'm using MS SQL Server 2008.

Upvotes: 2

Views: 2723

Answers (3)

i-one
i-one

Reputation: 5120

;with dm as (
    select distinct rm = datediff(mm, 0, dt)
    from #tbl t
)
select ms.[start], me.[end]
from dm
    cross apply (select dateadd(mm, dm.rm, 0)) ms([start])
    cross apply (select dateadd(ms, -3, dateadd(month, 1, ms.Value))) me([end])

The logic is following. For each date in the table find a number of months rm passed since date 0 (i.e. since January 1900, as cast(0 as datetime) is January 1 1900). To avoid duplicates distinct is used:

select distinct rm = datediff(mm, 0, dt)
from #tbl t

Above statement is wrapped into CTE named dm. Then for every row in dm calculate date of the beginning of the corresponding month using expression dateadd(mm, dm.rm, 0) (adding number of months passed since January 1900 will give beginning of the month even if the original date was somewhere in the middle of it). It is added to query with a cross apply construct since its value will be used not only in a select, but also for calculation of the month end date. End date is calculated by adding one month to start date and subtracting 3 milliseconds (so that end date would not be a date of start of the next month).

Upvotes: 1

aki
aki

Reputation: 41

For the end date, you could add 1 month to the first of the month's date then subtract 3 ms. 

SELECT 
    [end] = DATEADD(ms,-3, DATEADD(MM,1,DATEADD(MM,DATEDIFF(MM,0,dt),0)))

Upvotes: 0

Abe Miessler
Abe Miessler

Reputation: 85056

Try this:

SELECT DISTINCT DATEADD(month, DATEDIFF(month, 0, MyDate), 0) as startOfMonth,
                DATEADD(day, -1, DATEADD(month, 1, DATEADD(day, 1 - day(MyDate), MyDate))) as endOfMonth
FROM MyTable

Upvotes: 1

Related Questions