SQL: getting the start and end of day, week, month and year


Okay, that’s it!  I have officially had to re-figure-this-out one too many times: it’s going in the blog.

DECLARE @sometimestamp	AS DATETIME;
SET @sometimestamp = GETDATE();

SELECT 
@sometimestamp AS [date],

DATEADD(D, 0, DATEDIFF(D, 0, @sometimestamp)) 
AS [start of day],

DATEADD(S, -1, DATEDIFF(D, 0, DATEADD(D, 1, @sometimestamp))) 
AS [end of day],

DATEADD(D, 0, DATEDIFF(D, 0, DATEADD(D, -(DATEPART(dw, @sometimestamp) - 1), @sometimestamp)))
AS [start of week (SUN)],

DATEADD(S, -1, DATEDIFF(D, 0, DATEADD(D, 8 - DATEPART(dw, @sometimestamp), @sometimestamp))) 
AS [end of week (SAT)],

DATEADD(M, DATEDIFF(M, 0, @sometimestamp), 0) 
AS [start of month],

DATEADD(S, -1, DATEADD(M, DATEDIFF(M, 0, DATEADD(M, 1, @sometimestamp)), 0)) 
AS [end of month],

DATEADD(YY, DATEDIFF(YY, 0, @sometimestamp), 0) 
AS [start of year],

DATEADD(S, -1, DATEADD(YY, DATEDIFF(YY, 0, DATEADD(YY, 1, @sometimestamp)), 0)) 
AS [end of year]

There! Done! Not doing that again!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s