Search This Blog & Web

Thursday, July 30, 2009

first and last date of Month,Quarter,Week and year.

you can get a lot more by changing parameters a little

--- first & last day of month
select DATEADD(mm, DATEDIFF(mm, 0, GETDATE()),0)

select DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1 ,-1)

-- first & last day of week
select DATEADD(wk, DATEDIFF(wk, 0, GETDATE()),0)
select DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1 ,-1)


-- first & last day of year
select DATEADD(yy, DATEDIFF(yy, 0, GETDATE()),0)
select DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1 ,-1)

-- first & last day of Querter
select DATEADD(qq, DATEDIFF(qq, 0, GETDATE()),0)
select DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 1 ,-1)

5 comments:

Unknown said...

Thanks, It is helpful to me . I have used it to return week and day.

Hammad said...

Thanks, I got greate help from this post.

Anonymous said...

it really a good tips thanks.

Anonymous said...

I need first and last date of month and it helps me.


Thanks for sharing this on blog.

Shamas DBA said...

This function will return Last, First, Previous days of month with 00.00 time. If i want to return Maximum time 23.59 then I have to do this.

This is for last day of month.
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

Chaging mm to s in DateAdd function.