Useful T-SQL For Date Calculations

In this article, we will see some useful T-SQL for date calculation. Sometimes we need to get reports on daily, weekly, monthly basis.

Also we need data of the previous month and/or next month for some statistics.
 

--Today

SELECT DATEADD(s, 1, DATEADD(mm, DATEDIFF(m, 0, GETUTCDATE()), 0)) 'Today Start DateTime'

SELECT DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETUTCDATE()), 1)) 'Today End DateTime'

--This Week

Select DATEADD(wk, DATEDIFF(wk, 6, GETUTCDATE()), 6) 'This Week Start Date'

SELECT DAteADD(dd, 6, (SELECT DATEADD(wk, DATEDIFF(wk, 6, GETUTCDATE()), 6))) 'This Week End Date'

Select dateadd(s,1,DATEADD(wk, DATEDIFF(wk, 6, GETUTCDATE()), 6)) 'This Week Start DateTime'

SELECT dateadd(s,-1,DATEADD(dd, 6, (SELECT DATEADD(wk, DATEDIFF(wk, 6, GETUTCDATE()), 7)))) 'This Week End DateTime'

The result is:

 

 

--Next Week

SELECT DATEADD(wk, DATEDIFF(wk, 6, GETUTCDATE()) + 1, 6) 'Next Week Start Date'

SELECT DAteADD(dd, 6, (SELECT DATEADD(wk, DATEDIFF(wk, 6, GETUTCDATE()) + 1, 6))) 'Next Week End Date'

SELECT dateadd(s,1,DATEADD(wk, DATEDIFF(wk, 6, GETUTCDATE()) + 1, 6)) 'Next Week Start DateTime'

SELECT dateadd(s,-1,DAteADD(dd, 6, (SELECT DATEADD(wk, DATEDIFF(wk, 6, GETUTCDATE()) + 1, 7)))) 'Next Week End DateTime'

--This month

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETUTCDATE()), 0) 'This Month Start Date'

SELECT dateadd(d,-1,DATEADD(mm, DATEDIFF(m, 0, GETUTCDATE())+1,0 )) 'This Month End Date'

SELECT DATEADD(s,1, DATEADD(mm, DATEDIFF(mm, 0, GETUTCDATE()), 0)) 'This Month Start DateTime'

SELECT DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETUTCDATE()) + 1, 0)) 'This Month End DateTime'

The result is:

 

 

--Next Month

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETUTCDATE())+1, 0) As 'Next Month Start Date'

SELECT dateadd(d,-1,DATEADD(mm, DATEDIFF(m, 0, GETUTCDATE())+2,0 )) As 'Next Month End Date'

SELECT DATEADD(s,1, DATEADD(mm, DATEDIFF(mm, 0, GETUTCDATE())+1, 0)) 'Next Month Start DateTime'

SELECT DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETUTCDATE()) + 2, 0)) 'Next End DateTime'

--Previous Month

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETUTCDATE())-1, 0) As 'Prev Month Start Date'

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETUTCDATE()) , -1) As 'Prev Month End Date'

SELECT DATEADD(s,1, DATEADD(mm, DATEDIFF(mm, 0, GETUTCDATE())-1, 0)) 'Prev Month Start DateTime'

SELECT dateadd(d,1,dateadd(s,-1,DATEADD(mm, DATEDIFF(mm, 0, GETUTCDATE()) , -1))) 'Prev Month End DateTime'

The result is: