Introduction

Please check the below queries to get first and last day of the previous month (with timestamp) in SQL Server.
First Day Of Current Week.
  1. select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),0),106)
Last Day Of Current Week.
  1. select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),6),106)
First Day Of Last week.
  1. select CONVERT(varchar,DATEADD(week,datediff(week,7,getdate()),0),106)
Last Day Of Last Week.
  1. select CONVERT(varchar,dateadd(week,datediff(week,7,getdate()),6),106)
First Day Of Next Week.
  1. select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),7),106)
Last Day Of Next Week.
  1. select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),13),106)
First Day Of Current Month.
  1. select CONVERT(varchar,dateadd(d,-(day(getdate()-1)),getdate()),106)
Last Day Of Current Month.
  1. select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()))),dateadd(m,1,getdate())),106)
This example works on only if the date is 31 and remaining days are not.
First Day Of Last Month.
  1. select CONVERT(varchar,dateadd(d,-(day(dateadd(m,-1,getdate()-2))),dateadd(m,-1,getdate()-1)),106)
Last Day Of Last Month.
  1. select CONVERT(varchar,dateadd(d,-(day(getdate())),getdate()),106)
First Day Of Next Month.
  1. select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()-1))),dateadd(m,1,getdate())),106)
Last Day Of Next Month.
  1. select CONVERT(varchar,dateadd(d,-(day(dateadd(m,2,getdate()))),DATEADD(m,2,getdate())),106)
First Day Of Current Year.
  1. select CONVERT(varchar,dateadd(year,datediff(year,0,getdate()),0),106)
Last Day Of Current Year.
  1. select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+1,0))),106)
First Day of Last Year.
  1. select CONVERT(varchar,dateadd(year,datediff(year,0,getdate())-1,0),106)
Last Day Of Last Year.
  1. select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate()),0))),106)
First Day Of Next Year.
  1. select CONVERT(varchar,dateadd(YEAR,DATEDIFF(year,0,getdate())+1,0),106)
Last Day Of Next Year.
  1. select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+2,0))),106)

Conclusion

This will be very helpful to find out the particular day using getdate().

Comments

Join the conversation! Your thoughts help the community grow.