Get Specific Day Using getdate() Function

In this blog, you will learn about the tricks of getdate() function.

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.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),0),106)

Last Day Of Current Week.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),6),106)

First Day Of Last week. 

select CONVERT(varchar,DATEADD(week,datediff(week,7,getdate()),0),106)

Last Day Of Last Week. 

select CONVERT(varchar,dateadd(week,datediff(week,7,getdate()),6),106)

First Day Of Next Week. 

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),7),106)

Last Day Of Next Week. 

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),13),106)

First Day Of Current Month. 

select CONVERT(varchar,dateadd(d,-(day(getdate()-1)),getdate()),106)

Last Day Of Current Month. 

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.

select CONVERT(varchar,dateadd(d,-(day(dateadd(m,-1,getdate()-2))),dateadd(m,-1,getdate()-1)),106)

Last Day Of Last Month.

select CONVERT(varchar,dateadd(d,-(day(getdate())),getdate()),106)

First Day Of Next Month. 

select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()-1))),dateadd(m,1,getdate())),106)

Last Day Of Next Month.

select CONVERT(varchar,dateadd(d,-(day(dateadd(m,2,getdate()))),DATEADD(m,2,getdate())),106)

First Day Of Current Year. 

select CONVERT(varchar,dateadd(year,datediff(year,0,getdate()),0),106)

Last Day Of Current Year.

select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+1,0))),106)

First Day of Last Year.

select CONVERT(varchar,dateadd(year,datediff(year,0,getdate())-1,0),106)

Last Day Of Last Year. 

select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate()),0))),106)

First Day Of Next Year. 

select CONVERT(varchar,dateadd(YEAR,DATEDIFF(year,0,getdate())+1,0),106)

Last Day Of Next Year. 

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().