SQL Statements for First and Last Date of a Month and Week

by Bill Dowling 17. June 2013 11:26

The following tsql statements can be used to get the first and last dates in a month.

DECLARE @InputDate DATE=GETDATE()

SELECT DAY(DATEADD(MONTH,DATEDIFF(MONTH,-1,@InputDate),-1)) AS DaysInMonth

SELECT DATEADD(M,DATEDIFF(M, 0, @InputDate), 0) AS FirstDate

SELECT DATEADD(D,-1,DATEADD(M,DATEDIFF(M,0,@InputDate)+1,0)) AS LastDate

SELECT DATEADD(S,-1,DATEADD(M,DATEDIFF(M,0,@InputDate)+1,0)) AS LastWithTime

SELECT DateAdd(MONTH,DateDiff(MONTH, 0,GetDate())-1, 0) AS FirstDateLastMonth

SELECT DateAdd(MONTH,DateDiff(MONTH,-1,GetDate())-1,-1) AS LastDateLastMonth

SELECT CAST(GETDATE()-DATEPART(DW,GETDATE())+ 1 AS date) AS FirstDateOfWeek

SELECT CAST(GETDATE()+(7 -DATEPART(DW,GETDATE())) AS date) AS LastDateOfWeek 

SELECT DATEADD(MONTH,((FourDigitYear - 1900)* 12)+MonthNmbr - 1, 0) AS FirstDayInMonth

 

Tags:

SQL Coding Examples

Add comment

About the author

Bill Dowling has been a VB and .Net programmer for as long as he can remember.

Month List