How to get first and last day of previous month (with timestamp) in SQL Server

I could not find the solution which gives first and last day of previous month with timestamp.

Here is the solution.

SELECT DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0) as FirtDayPreviousMonthWithTimeStamp,
    DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) as LastDayPreviousMonthWithTimeStamp

This will return the following if currentdate = '2012-7-31'

result: 2012-06-01 00:00:00.000 2012-06-30 23:59:59.000

This will return the following if currentdate = '2012-1-1'

result: 2011-12-01 00:00:00.000 2011-12-31 23:59:59.000

select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) --First day of previous month
select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) --Last Day of previous month

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)

In this Example Works on Only 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)

SELECT CONVERT(DATE,DATEADD(MM, DATEDIFF(MM, 0, GETDATE())-1, 0)) AS FirstDayOfPrevMonth
SELECT CONVERT(DATE,DATEADD(MS, -3, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) , 0))) AS LastDayOfPrevMonth

Solution

>The date format that you requested is called ODBC format (code 120).

To actually calculate the values that you requested, include the following in your SQL.

>Copy, paste...

DECLARE
	@FirstDayOfLastMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, -1, GETDATE() - 2)) ), DATEADD(m, -1, GETDATE() - 1)))
	, @LastDayOfLastMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(GETDATE()) ), GETDATE()))

>...and use in your code:

>- @FirstDayOfLastMonth

• @LastDayOfLastMonth

Be aware that it has to be pasted earlier than any statements that reference the parameters, but from that point on you can reference @FirstDayOfLastMonth and @LastDayOfLastMonth in your code.

Example

Let's see some code in action:

DECLARE
	@FirstDayOfLastMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, -1, GETDATE() - 2)) ), DATEADD(m, -1, GETDATE() - 1)))
	, @LastDayOfLastMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(GETDATE()) ), GETDATE()))

SELECT
	'First day of last month' AS Title, CONVERT(VARCHAR, @FirstDayOfLastMonth , 120) AS [ODBC]
UNION 

SELECT 
	'Last day of last month' AS Title, CONVERT(VARCHAR, @LastDayOfLastMonth , 120) AS [ODBC]

Run the above code to produce the following output:

Note: Bear in mind that today's date for me is 12th September, 2016.

>More (for completeness' sake)

Common date parameters

Are you left wanting more?

To set up a more comprehensive range of handy date related parameters, include the following in your SQL:

DECLARE
	@FirstDayOfCurrentWeek DATETIME = CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
	, @LastDayOfCurrentWeek DATETIME = CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 6))
	, @FirstDayOfLastWeek DATETIME = CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 7, GETDATE()), 0))
	, @LastDayOfLastWeek DATETIME = CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 7, GETDATE()), 6))
	, @FirstDayOfNextWeek DATETIME = CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 7))
	, @LastDayOfNextWeek DATETIME = CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 13))
	, @FirstDayOfCurrentMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(GETDATE() - 1) ), GETDATE()))
	, @LastDayOfCurrentMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, 1, GETDATE())) ), DATEADD(m, 1, GETDATE())))
	, @FirstDayOfLastMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, -1, GETDATE() - 2)) ), DATEADD(m, -1, GETDATE() - 1)))
	, @LastDayOfLastMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(GETDATE()) ), GETDATE()))
	, @FirstDayOfNextMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, 1, GETDATE() - 1)) ), DATEADD(m, 1, GETDATE())))
	, @LastDayOfNextMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, 2, GETDATE())) ), DATEADD(m, 2, GETDATE())))
	, @FirstDayOfCurrentYear DATETIME = CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
	, @LastDayOfCurrentYear DATETIME = CONVERT(DATE, DATEADD(ms, -2, DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0))))
	, @FirstDayOfLastYear DATETIME = CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0))
	, @LastDayOfLastYear DATETIME = CONVERT(DATE, DATEADD(ms, -2, DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))))
	, @FirstDayOfNextYear DATETIME = CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0))
	, @LastDayOfNextYear DATETIME = CONVERT(DATE, DATEADD(ms, -2, DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 2, 0))))

It would make most sense to include it earlier on, preferably at the top of your procedure or SQL query.

Once declared, the parameters can be referenced anywhere in your code, as many times as you need them.

Example

Let's see some code in action:

DECLARE
	@FirstDayOfCurrentWeek DATETIME = CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
	, @LastDayOfCurrentWeek DATETIME = CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 6))
	, @FirstDayOfLastWeek DATETIME = CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 7, GETDATE()), 0))
	, @LastDayOfLastWeek DATETIME = CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 7, GETDATE()), 6))
	, @FirstDayOfNextWeek DATETIME = CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 7))
	, @LastDayOfNextWeek DATETIME = CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 13))
	, @FirstDayOfCurrentMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(GETDATE() - 1) ), GETDATE()))
	, @LastDayOfCurrentMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, 1, GETDATE())) ), DATEADD(m, 1, GETDATE())))
	, @FirstDayOfLastMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, -1, GETDATE() - 2)) ), DATEADD(m, -1, GETDATE() - 1)))
	, @LastDayOfLastMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(GETDATE()) ), GETDATE()))
	, @FirstDayOfNextMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, 1, GETDATE() - 1)) ), DATEADD(m, 1, GETDATE())))
	, @LastDayOfNextMonth DATETIME = CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, 2, GETDATE())) ), DATEADD(m, 2, GETDATE())))
	, @FirstDayOfCurrentYear DATETIME = CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
	, @LastDayOfCurrentYear DATETIME = CONVERT(DATE, DATEADD(ms, -2, DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0))))
	, @FirstDayOfLastYear DATETIME = CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0))
	, @LastDayOfLastYear DATETIME = CONVERT(DATE, DATEADD(ms, -2, DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))))
	, @FirstDayOfNextYear DATETIME = CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0))
	, @LastDayOfNextYear DATETIME = CONVERT(DATE, DATEADD(ms, -2, DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 2, 0))))

SELECT  
	'a) FirstDayOfCurrentWeek.' AS [Title] ,
	@FirstDayOfCurrentWeek AS [DATE (Server default)] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentWeek, 127) AS [ISO8601] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentWeek, 103) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentWeek, 104) + ' ' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentWeek, 105) + ' ' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentWeek, 111) + ' ' + CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentWeek, 100) AS [U.S.] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentWeek, 120) AS [ODBC]
UNION
SELECT  
	'b) LastDayOfCurrentWeek.' AS [Title] ,
	@LastDayOfCurrentWeek AS [DATE (Server default)] ,
	CONVERT(VARCHAR, @LastDayOfCurrentWeek, 127) AS [ISO8601] ,
	CONVERT(VARCHAR, @LastDayOfCurrentWeek, 103) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] ,
	CONVERT(VARCHAR, @LastDayOfCurrentWeek, 104) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] ,
	CONVERT(VARCHAR, @LastDayOfCurrentWeek, 105) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] ,
	CONVERT(VARCHAR, @LastDayOfCurrentWeek, 111) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] ,
	CONVERT(VARCHAR, @LastDayOfCurrentWeek, 100) AS [U.S.] ,
	CONVERT(VARCHAR, @LastDayOfCurrentWeek, 120) AS [ODBC]
UNION
SELECT  
	'c) FirstDayOfLastWeek.' AS [Title] ,
	@FirstDayOfLastWeek AS [DATE (Server default)] ,
	CONVERT(VARCHAR, @FirstDayOfLastWeek, 127) AS [ISO8601] ,
	CONVERT(VARCHAR, @FirstDayOfLastWeek, 103) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] ,
	CONVERT(VARCHAR, @FirstDayOfLastWeek, 104) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] ,
	CONVERT(VARCHAR, @FirstDayOfLastWeek, 105) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] ,
	CONVERT(VARCHAR, @FirstDayOfLastWeek, 111) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] ,
	CONVERT(VARCHAR, @FirstDayOfLastWeek, 100) AS [U.S.] ,
	CONVERT(VARCHAR, @FirstDayOfLastWeek, 120) AS [ODBC]
UNION
SELECT  
	'd) LastDayOfLastWeek.' AS [Title] ,
	@LastDayOfLastWeek AS [DATE (Server default)] ,
	CONVERT(VARCHAR, @LastDayOfLastWeek, 127) AS [ISO8601] ,
	CONVERT(VARCHAR, @LastDayOfLastWeek, 103) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] ,
	CONVERT(VARCHAR, @LastDayOfLastWeek, 104) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] ,
	CONVERT(VARCHAR, @LastDayOfLastWeek, 105) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] ,
	CONVERT(VARCHAR, @LastDayOfLastWeek, 111) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] ,
	CONVERT(VARCHAR, @LastDayOfLastWeek, 100) AS [U.S.] ,
	CONVERT(VARCHAR, @LastDayOfLastWeek, 120) AS [ODBC]
UNION
SELECT  
	'e) FirstDayOfNextWeek.' AS [Title] ,
	@FirstDayOfNextWeek AS [DATE (Server default)] ,
	CONVERT(VARCHAR, @FirstDayOfNextWeek, 127) AS [ISO8601] ,
	CONVERT(VARCHAR, @FirstDayOfNextWeek, 103) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] ,
	CONVERT(VARCHAR, @FirstDayOfNextWeek, 104) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] ,
	CONVERT(VARCHAR, @FirstDayOfNextWeek, 105) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] ,
	CONVERT(VARCHAR, @FirstDayOfNextWeek, 111) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] ,
	CONVERT(VARCHAR, @FirstDayOfNextWeek, 100) AS [U.S.] ,
	CONVERT(VARCHAR, @FirstDayOfNextWeek, 120) AS [ODBC]
UNION
SELECT  
	'f) LastDayOfNextWeek.' AS [Title] ,
	@LastDayOfNextWeek AS [DATE (Server default)] ,
	CONVERT(VARCHAR, @LastDayOfNextWeek, 127) AS [ISO8601] ,
	CONVERT(VARCHAR, @LastDayOfNextWeek, 103) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] ,
	CONVERT(VARCHAR, @LastDayOfNextWeek, 104) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] ,
	CONVERT(VARCHAR, @LastDayOfNextWeek, 105) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] ,
	CONVERT(VARCHAR, @LastDayOfNextWeek, 111) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] ,
	CONVERT(VARCHAR, @LastDayOfNextWeek, 100) AS [U.S.] ,
	CONVERT(VARCHAR, @LastDayOfNextWeek, 120) AS [ODBC]
UNION
SELECT  
	'g) FirstDayOfCurrentMonth.' AS [Title] ,
	@FirstDayOfCurrentMonth AS [DATE (Server default)] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentMonth, 127) AS [ISO8601] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentMonth, 103) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentMonth, 104) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentMonth, 105) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentMonth, 111) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentMonth, 100) AS [U.S.] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentMonth, 120) AS [ODBC]
UNION
SELECT  
	'h) LastDayOfCurrentMonth.' AS [Title] ,
	@LastDayOfCurrentMonth AS [DATE (Server default)] ,
	CONVERT(VARCHAR, @LastDayOfCurrentMonth, 127) AS [ISO8601] ,
	CONVERT(VARCHAR, @LastDayOfCurrentMonth, 103) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] ,
	CONVERT(VARCHAR, @LastDayOfCurrentMonth, 104) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] ,
	CONVERT(VARCHAR, @LastDayOfCurrentMonth, 105) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] ,
	CONVERT(VARCHAR, @LastDayOfCurrentMonth, 111) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] ,
	CONVERT(VARCHAR, @LastDayOfCurrentMonth, 100) AS [U.S.] ,
	CONVERT(VARCHAR, @LastDayOfCurrentMonth, 120) AS [ODBC]
UNION
SELECT  
	'i) FirstDayOfLastMonth.' AS [Title] ,
	@FirstDayOfLastMonth AS [DATE (Server default)] ,
	CONVERT(VARCHAR, @FirstDayOfLastMonth, 127) AS [ISO8601] ,
	CONVERT(VARCHAR, @FirstDayOfLastMonth, 103) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] ,
	CONVERT(VARCHAR, @FirstDayOfLastMonth, 104) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] ,
	CONVERT(VARCHAR, @FirstDayOfLastMonth, 105) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] ,
	CONVERT(VARCHAR, @FirstDayOfLastMonth, 111) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] ,
	CONVERT(VARCHAR, @FirstDayOfLastMonth, 100) AS [U.S.] ,
	CONVERT(VARCHAR, @FirstDayOfLastMonth, 120) AS [ODBC]
UNION
SELECT  
	'j) LastDayOfLastMonth.' AS [Title] ,
	@LastDayOfLastMonth AS [DATE (Server default)] ,
	CONVERT(VARCHAR, @LastDayOfLastMonth, 127) AS [ISO8601] ,
	CONVERT(VARCHAR, @LastDayOfLastMonth, 103) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] ,
	CONVERT(VARCHAR, @LastDayOfLastMonth, 104) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] ,
	CONVERT(VARCHAR, @LastDayOfLastMonth, 105) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] ,
	CONVERT(VARCHAR, @LastDayOfLastMonth, 111) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] ,
	CONVERT(VARCHAR, @LastDayOfLastMonth, 100) AS [U.S.] ,
	CONVERT(VARCHAR, @LastDayOfLastMonth, 120) AS [ODBC]
UNION
SELECT  
	'k) FirstDayOfNextMonth.' AS [Title] ,
	@FirstDayOfNextMonth AS [DATE (Server default)] ,
	CONVERT(VARCHAR, @FirstDayOfNextMonth, 127) AS [ISO8601] ,
	CONVERT(VARCHAR, @FirstDayOfNextMonth, 103) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] ,
	CONVERT(VARCHAR, @FirstDayOfNextMonth, 104) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] ,
	CONVERT(VARCHAR, @FirstDayOfNextMonth, 105) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] ,
	CONVERT(VARCHAR, @FirstDayOfNextMonth, 111) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] ,
	CONVERT(VARCHAR, @FirstDayOfNextMonth, 100) AS [U.S.] ,
	CONVERT(VARCHAR, @FirstDayOfNextMonth, 120) AS [ODBC]
UNION
SELECT  
	'l) LastDayOfNextMonth.' AS [Title] ,
	@LastDayOfNextMonth AS [DATE (Server default)] ,
	CONVERT(VARCHAR, @LastDayOfNextMonth, 127) AS [ISO8601] ,
	CONVERT(VARCHAR, @LastDayOfNextMonth, 103) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] ,
	CONVERT(VARCHAR, @LastDayOfNextMonth, 104) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] ,
	CONVERT(VARCHAR, @LastDayOfNextMonth, 105) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] ,
	CONVERT(VARCHAR, @LastDayOfNextMonth, 111) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] ,
	CONVERT(VARCHAR, @LastDayOfNextMonth, 100) AS [U.S.] ,
	CONVERT(VARCHAR, @LastDayOfNextMonth, 120) AS [ODBC]
UNION
SELECT  
	'm) FirstDayOfCurrentYear.' AS [Title] ,
	@FirstDayOfCurrentYear AS [DATE (Server default)] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentYear, 127) AS [ISO8601] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentYear, 103) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentYear, 104) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentYear, 105) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentYear, 111) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentYear, 100) AS [U.S.] ,
	CONVERT(VARCHAR, @FirstDayOfCurrentYear, 120) AS [ODBC]
UNION
SELECT  
	'n) LastDayOfCurrentYear.' AS [Title] ,
	@LastDayOfCurrentYear AS [DATE (Server default)] ,
	CONVERT(VARCHAR, @LastDayOfCurrentYear, 127) AS [ISO8601] ,
	CONVERT(VARCHAR, @LastDayOfCurrentYear, 103) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] ,
	CONVERT(VARCHAR, @LastDayOfCurrentYear, 104) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] ,
	CONVERT(VARCHAR, @LastDayOfCurrentYear, 105) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] ,
	CONVERT(VARCHAR, @LastDayOfCurrentYear, 111) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] ,
	CONVERT(VARCHAR, @LastDayOfCurrentYear, 100) AS [U.S.] ,
	CONVERT(VARCHAR, @LastDayOfCurrentYear, 120) AS [ODBC]
UNION
SELECT  
	'o) FirstDayOfLastYear.' AS [Title] ,
	@FirstDayOfLastYear AS [DATE (Server default)] ,
	CONVERT(VARCHAR, @FirstDayOfLastYear, 127) AS [ISO8601] ,
	CONVERT(VARCHAR, @FirstDayOfLastYear, 103) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] ,
	CONVERT(VARCHAR, @FirstDayOfLastYear, 104) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] ,
	CONVERT(VARCHAR, @FirstDayOfLastYear, 105) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] ,
	CONVERT(VARCHAR, @FirstDayOfLastYear, 111) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] ,
	CONVERT(VARCHAR, @FirstDayOfLastYear, 100) AS [U.S.] ,
	CONVERT(VARCHAR, @FirstDayOfLastYear, 120) AS [ODBC]
UNION
SELECT  
	'p) LastDayOfLastYear.' AS [Title] ,
	@LastDayOfLastYear AS [DATE (Server default)] ,
	CONVERT(VARCHAR, @LastDayOfLastYear, 127) AS [ISO8601] ,
	CONVERT(VARCHAR, @LastDayOfLastYear, 103) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] ,
	CONVERT(VARCHAR, @LastDayOfLastYear, 104) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] ,
	CONVERT(VARCHAR, @LastDayOfLastYear, 105) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] ,
	CONVERT(VARCHAR, @LastDayOfLastYear, 111) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] ,
	CONVERT(VARCHAR, @LastDayOfLastYear, 100) AS [U.S.] ,
	CONVERT(VARCHAR, @LastDayOfLastYear, 120) AS [ODBC]
UNION
SELECT  
	'q) FirstDayOfNextYear.' AS [Title] ,
	@FirstDayOfNextYear AS [DATE (Server default)] ,
	CONVERT(VARCHAR, @FirstDayOfNextYear, 127) AS [ISO8601] ,
	CONVERT(VARCHAR, @FirstDayOfNextYear, 103) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] ,
	CONVERT(VARCHAR, @FirstDayOfNextYear, 104) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] ,
	CONVERT(VARCHAR, @FirstDayOfNextYear, 105) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] ,
	CONVERT(VARCHAR, @FirstDayOfNextYear, 111) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] ,
	CONVERT(VARCHAR, @FirstDayOfNextYear, 100) AS [U.S.] ,
	CONVERT(VARCHAR, @FirstDayOfNextYear, 120) AS [ODBC]
UNION
SELECT  
	'r) LastDayOfNextYear.' AS [Title] ,
	@LastDayOfNextYear AS [DATE (Server default)] ,
	CONVERT(VARCHAR, @LastDayOfNextYear, 127) AS [ISO8601] ,
	CONVERT(VARCHAR, @LastDayOfNextYear, 103) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [British&French] ,
	CONVERT(VARCHAR, @LastDayOfNextYear, 104) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [German] ,
	CONVERT(VARCHAR, @LastDayOfNextYear, 105) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Italian] ,
	CONVERT(VARCHAR, @LastDayOfNextYear, 111) + ' ' +  CONVERT(CHAR(5), @FirstDayOfCurrentWeek, 108) AS [Japan] ,
	CONVERT(VARCHAR, @LastDayOfNextYear, 100) AS [U.S.] ,
	CONVERT(VARCHAR, @LastDayOfNextYear, 120) AS [ODBC];

Run the above code to produce the following output:

If your country is missing, then it is because I don't know the code for it. It would be most helpful and appreciated if you could please edit this answer and add a new column for your country.

Thanks in advance.

Note: Bear in mind that today's date for me is 12th September, 2016.

References

For further reading on the ISO8601 international date standard, follow this link:

• https://en.wikipedia.org/wiki/ISO_8601

For further reading on the ODBC international date standard, follow this link:

• https://technet.microsoft.com/en-us/library/ms190234(v=sql.90).aspx

To view the list of date formats I worked from, follow this link:

• https://msdn.microsoft.com/en-GB/library/ms187928.aspx

For further reading on the DATETIME data type, follow this link:

• https://msdn.microsoft.com/en-GB/library/ms187819.aspx

From SQL2012, there is a new function introduced called EOMONTH. Using this function the first and last day of the last month can be easily found.

select DATEADD(DD,1,EOMONTH(Getdate(),-2)) firstdayoflastmonth, EOMONTH(Getdate(), -1) lastdayoflastmonth

SELECT DATEADD(m,DATEDIFF(m,0,GETDATE())-1,0) AS PreviousMonthStart

SELECT DATEADD(ms,-2,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) AS PreviousMonthEnd
	

To get last month's first date:

select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) LastMonthFirstDate                                                                

To get last month's last date:

select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) LastMonthEndDate

I have used the following logic in SSRS reports.

BUS_DATE = 17-09-2013

X=DATEADD(MONTH,-1,BUS_DATE) = 17-08-2013

Y=DAY(BUS_DATE)=17

first_date = DATEADD(DAY,-Y+1,X)=01-08-2013

last_date  = DATEADD(DAY,-Y,BUS_DATE)=31-08-2013

You can get first and last day of previous month (with timestamp) in SQL Server by executing

--select dateadd(dd,-datepart(dd,getdate())+1,dateadd(mm,-1,getdate())) --first day of previous month 
--select dateadd(dd,-datepart(dd,getdate()),getdate()) -- last day of previous month**

Take some base date which is the 31st of some month e.g. '20011231'. Then use the
following procedure (I have given 3 identical examples below, only the @dt value differs).

declare @dt datetime;

set @dt = '20140312'

SELECT DATEADD(month, DATEDIFF(month, '20011231', @dt), '20011231');



set @dt = '20140208'

SELECT DATEADD(month, DATEDIFF(month, '20011231', @dt), '20011231');



set @dt = '20140405'

SELECT DATEADD(month, DATEDIFF(month, '20011231', @dt), '20011231');

Here's a fairly straight forward and dynamic way. For last month's last day, subtract the current numeric day from today's date. For first day of last month, use the same code, just repeat subtracting the numeric day from the prior result and add 1.

declare @PriorEOM as datetime
declare @PriorFOM as datetime

Set @PriorEOM = dateadd(day,-day(getdate()),getdate())

Set @PriorFOM = dateadd(day,-day(@PriorEOM) + 1,@PriorEOM)

Print @PriorEOM
Print @PriorFOM

I've not seen this solution presented yet; this is my preference for its simpler readability:

select dateadd(month,-1,format(getutcdate(),'yyyy-MM-01'))