Date functions MSSQL

This Getdate:

2024-05-03 05:26:37

Month Year

Select DateName(Month,getdate())+’ ‘+ DateName(YYYY,getdate())

Result:
May 2024

Previous month end date

Select cast(floor(cast(  DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0))as float)) as datetime)

Result:
2024-04-30

Previous month start date

Select cast(floor(cast(  DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)) as float)) as datetime)

Result:
2024-04-01

Current date + Hours, Minutes, Seconds

Select getdate()

Result:
2024-05-03 05:26:37

Today’s date + Hours, Minutes, Seconds & Milliseconds rounded to zeros 0’s

Select cast(floor(cast(getdate() as float))as datetime)

Current month last date

Select cast(floor(cast(dateadd(mm,1,getdate() – day(getdate())+1)-1 as float))as datetime)

Result:
2024-05-31

Last day of previous 12 Months

Select cast(floor(cast(DATEADD(m, – 12, getdate()  – day(getdate())+1)-1 as float))as datetime)

Result:
2023-05-31

Monday this week, start of this week

Select DATEADD(wk, DATEDIFF(wk,0,getdate()),0)

Result:
2024-04-29

Monday last week, start of last week

Select DATEADD(wk, DATEDIFF(wk,0,getdate()),-7)

Result:
2024-04-22

Sunday last week, end of last week.

Select cast(floor(cast(dateadd(d,-1,getdate()) as float))as datetime)

OR

Select DATEADD(wk, DATEDIFF(wk,0,getdate()),-1)

Result:
2024-04-28

First day of current calendar year

Select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

Result:
2024-01-04

First day of last calendar year

Select DATEADD(yy, DATEDIFF(yy,0,dateadd(mi,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))), 0)

Result:
2023-01-04

Last day of previous calendar year

Select cast(floor(cast(dateadd(mi,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) as float)) as datetime)

Result:
2023-12-02

First day of next calendar year

Select DATEADD(yy, DATEDIFF(yy,-1,getdate()), 0)

Result:
2025-12-02

This week last year. Monday date, week start

Select cast(floor(cast(dateadd(wk,datediff(wk,0,dateadd(yy,-1,getdate()) ),0)as float)) as datetime)

Result:
2023-04-22

Todays date last year

Select dateadd(yy,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()),0))

Result:
2023-05-03

First day of current month

Select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

Result:
2024-05-04

Last day of current month

Select cast(floor(cast(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))as float)) as datetime)

Result:
2024-05-02

Last day of current year

Select cast(floor(cast(dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1, 0))as float)) as datetime)

Result:
2024-12-31

First day of current month last year

Select dateadd(yy,-1,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

Result:
2023-05-01

Last day of the current month last year

Select cast(floor(cast(dateadd(yy,-1,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0)))as float)) as datetime)

Result:
2023-05-31

First day 6 months ago

Select dateAdd(mm,-6,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

Result:
2023-11-01