Last datetime (or beginning) of the month, week, and year with sql


Some usefull datetime functions. To retrieve this month records, it can be done this way:

….WHERE checkedintime>DATEADD(s,-1, DATEADD(month, DATEDIFF(month,0,GETDATE()),0))  AND checkedintime< =DATEADD(s,-1, DATEADD(month, DATEDIFF(month,0,GETDATE())+1,0))

–SELECT DATEADD(s,-1, DATEADD(month, DATEDIFF(month,0,GETDATE())+1,0)) as LastDayTime_CurrentMonth

–SELECT DATEADD(s,-1, DATEADD(month, DATEDIFF(month,0,GETDATE()),0)) as LastDayTime_PreviousMonth

 
A list of these functions for other usage.
–Quarter
SELECT  DATEADD(Q, DATEDIFF(Q, 0, getdate()) +1 , 0) as endOfQuarter,

DATEADD

(Q, DATEDIFF(Q, 0, getdate()) , 0) as beginningQuarter

SELECT

DATEADD(DD, 1 DATEPART(DW, getdate()), DateDiff(day, 0, GETDATE())) as beginningOfThisSunday

SELECT

DATEADD(DD, 1 DATEPART(DW, getdate()), 7 + DateDiff(day, 0, GETDATE())) as beginningOfNextSunday

SELECT

DATEADD(DD, 1 DATEPART(day, getdate()), DateDiff(day, 0, GETDATE())) as beginningOfThisMonth

or

SELECT

DATEADD(mm, DATEDIFF(mm,0,GETDATE())-1,0) as StartOfLastMonth

SELECT

DATEADD(mm, DATEDIFF(mm,0,GETDATE()),0) as StartOfThisMonth

SELECT

DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0) as StartOfNextMonth

 

SELECT

DATEADD(day, DATEDIFF(day,0,GETDATE()),0) as StartOftheday

SELECT

DATEADD(day, DATEDIFF(day,0,GETDATE())+1,0) as StartOfNextday

 

SELECT

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) as LastDay_CurrentMonth

SELECT

DATEADD(s,-1, DATEADD(day, DATEDIFF(day,0,GETDATE()),0)) as LastDayTime_PreviousDay

SELECT

DATEADD(s,-1, DATEADD(day, DATEDIFF(day,0,GETDATE())+1,0)) as LastDayTime_CurrentDay

SELECT

DATEADD(s,-1, DATEADD(year, DATEDIFF(year,0,GETDATE()),0)) as LastDayTime_PreviousYear

SELECT

DATEADD(s,-1, DATEADD(year, DATEDIFF(year,0,GETDATE())+1,0)) as LastDayTime_CurrentYear

SELECT

DATEADD(s,-1, DATEADD(week, DATEDIFF(week,0,GETDATE()),0)) as LastDayTime_PreviousWeek

SELECT

DATEADD(s,-1, DATEADD(week, DATEDIFF(week,0,GETDATE())+1,0)) as LastDayTime_CurrentWeek

 

SELECT

DATEADD(s,-1, DATEADD(month, DATEDIFF(month,0,GETDATE())+1,0)) as LastDayTime_CurrentMonth

SELECT

DATEADD(s,-1, DATEADD(month, DATEDIFF(month,0,GETDATE()),0)) as LastDayTime_PreviousMonth

SELECT

  DATEADD(ms,-3, DATEADD(year, DATEDIFF(year,0,GETDATE()),0)) as LastDayTimeOfPreviousYear

SELECT

DATEADD(ms,-3, DATEADD(year, DATEDIFF(year,0,GETDATE())+1,0)) as beginningOfNextYear

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