Important Date Function In Sql Server 2008

Here are the some of important date function which i have worked out last week in order to write some Sp’s for my project.

Start and End of Today
SELECT DATEADD(DD,DATEDIFF(DD,0, GETDATE()),0) as [Start Of Day]
DATEADD(s,-1,DATEADD(dd, DATEDIFF(dd,0,GETDATE())+1,0)) as [End Of Day]

Start and End of Week

SELECT DATEADD(WK, DATEDIFF(WK,0,GETDATE()),0) as [Start Of Week]
DATEADD(s,-1,DATEADD(WK, DATEDIFF(WK,0,GETDATE())+1,0)) as [End Of Week]

Start and End of Month

SELECT DATEADD(M, DATEDIFF(M,0,GETDATE()),0) as [Start Of Month]
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) as [End Of Month]

Start and End of Next Month

SELECT DATEADD(DD,DATEDIFF(DD,0,DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,1,GETDATE()))),0) as [Start of Next Month]
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)) as [End Of Next Month]

Start and End of Quarter

SELECT DATEADD(QQ, DATEDIFF(QQ,0,GETDATE()),0) as [Start Of Qaurter]
DATEADD(s,-1,DATEADD(QQ, DATEDIFF(QQ,0,GETDATE())+1,0)) as [End Of Quarter]

Start and End of Year

SELECT DATEADD(YY, DATEDIFF(YY,0,GETDATE()),0) as [Start Of Year]
DATEADD(s,-1,DATEADD(YY, DATEDIFF(YY,0,GETDATE())+1,0)) as [End Of Year]

Start and End of previous Month

SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(m,0,GETDATE()),0))
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

Last Year Start and End Date

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, -1, GetDate())), 0)
SELECT DATEADD(MILLISECOND, -3, DATEADD(YEAR,DATEDIFF(YEAR, 0, DATEADD(YEAR, -1, GETDATE())) + 1, 0))

Next Year Start and End Date

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0 DATEADD(YEAR,1,GETDATE())), 0),
SELECT DATEADD(MILLISECOND, -3,DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, 1,
GETDATE())) + 1, 0))

Let me know if you need anything more on this.

Thank You !

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: