Useful, dynamically-generated dates for SQL Server.

--[1st day of current month]: use the GetDate(), Month(), and Year() functions to determine the current month and year. Build a string using these values in MM/01/YYYY format. Convert the String to DateTime.
CAST( CAST( Month(GetDate()) AS Char(2) ) + '/01/' + CAST( Year(GetDate()) AS Char(4)) AS DateTime)

--[Last day of current month]: add a month to [1st day of current month], then subtract a day.
DateAdd(m, 1, CAST( CAST( Month(GetDate()) AS Char(2) ) + '/01/' + CAST( Year(GetDate()) AS Char(4)) AS DateTime)) -1

--[1st day of prior month]: subtract a month from [1st day of current month].
DateAdd(m, -1, CAST( CAST( Month(GetDate()) AS Char(2) ) + '/01/' + CAST( Year(GetDate()) AS Char(4)) AS DateTime))

--[Last day of prior month]: subtract a day from [1st day of current month].
CAST( CAST( Month(GetDate()) AS Char(2) ) + '/01/' + CAST( Year(GetDate()) AS Char(4)) AS DateTime) -1


Date · Programming · SQL · SQL Server · T-SQL