Fun With Dates in Microsoft SQL
Posted by: Adam Plocher on June 07, 2006
I needed a query that returned the last day of the month. After some research I found what I was looking for, but why stop there? The following query will generate several pieces of information based on todays date/time, including: the beginning and end of the day, beginning and end of the week, beginning and end of the month and finally, the beginning and end of the year.
I have tested this in MS SQL 2005 but it should work in MS SQL 2000, as well.
I have tested this in MS SQL 2005 but it should work in MS SQL 2000, as well.
DECLARE @Date AS DateTime
SET @Date = GETDATE()
SELECT
DateAdd(day, DateDiff(day, 0, @Date), 0) AS DayStart,
DateAdd(second, -1, DateAdd(day, DateDiff(day, 0, @Date)+1, 0) ) AS DayEnd,
DateAdd(week, DateDiff(week, 0, @Date), 0) AS WeekStart,
DateAdd(second, -1, DateAdd(week, DateDiff(week, 0, @Date)+1, 0) ) AS WeedEnd,
DateAdd(month, DateDiff(month, 0, @Date), 0) AS MonthStart,
DateAdd(second, -1, DateAdd(month, DateDiff(month, 0, @Date)+1, 0) ) AS MonthEnd,
DateAdd(year, DateDiff(year, 0, @Date), 0) AS YearStart,
DateAdd(second, -1, DateAdd(year, DateDiff(year, 0, @Date)+1, 0) ) AS YearEnd
Comments temporarily disabled
