The date time data type and the date time functions within SQL are things that I see coming up time and time again in news groups and forums. Questions on how to get rid of the time, how to get the first day of the week, the last day of the month and so on. With the new Date and Time data types coming in SQL 2008, things will get easier, nut until then we have to do things the hard way.<\/p>\n
In systems I’ve worked on I’ve seen several implementations of functions to find the first and last day of a week, a month or a quarter. Some have worked well, some have worked and others, well, haven’t<\/p>\n
So, here are some of the date functions that I use. The times are set so that these can be used in between statements and not pick up entries for the next interval.<\/p>\n
DECLARE @TheDate DATETIME\r\nSET @TheDate = GetDate()\r\n\r\n\/* beginning of the day *\/\r\nselect dateadd(dd, datediff(dd,0, @TheDate),0)\r\n\/* end of the day*\/\r\nselect dateadd(ms,-3,dateadd(dd, datediff(dd,0, @TheDate)+1,0))\r\n\r\n\/* beginning of the week. Beware DATEFIRST settings.*\/\r\nselect dateadd(ww, datediff(ww,0, @TheDate),0)\r\n\/* end of the week *\/\r\nselect dateadd(ms,-3,dateadd(ww, datediff(ww,0, @TheDate)+1,0))\r\n\r\n\/* beginning of the month *\/\r\nselect dateadd(mm, datediff(mm,0, @TheDate),0)\r\n\/* end of the month. Does account for leap years*\/\r\nselect dateadd(ms,-3,dateadd(mm, datediff(mm,0, @TheDate)+1,0))\r\n\r\n\/* beginning of the quarter*\/\r\nselect dateadd(qq, datediff(qq,0, @TheDate),0)\r\n\/* end of the quarter *\/\r\nselect dateadd(ms,-3,dateadd(qq, datediff(qq,0, @TheDate)+1,0))\r\n\r\n\/* beginning of the year *\/\r\nselect dateadd(yy, datediff(yy,0, @TheDate),0)\r\n\/* end of the year *\/\r\nselect dateadd(ms,-3,dateadd(yy, datediff(yy,0, @TheDate)+1,0))<\/pre>\nFor ease, these can be created as UDFs.<\/p>\n
-- The day, without the time\r\nCREATE FUNCTION dbo.DayStarts(@TheDate DATETIME)\r\nRETURNS DATETIME AS\r\nBEGIN\r\nRETURN dateadd(dd, datediff(dd,0, @TheDate),0)\r\nEND\r\nGO\r\n\r\n-- The day, 3 ms to midnight\r\nCREATE FUNCTION dbo.DayEnds(@TheDate DATETIME)\r\nRETURNS DATETIME AS\r\nBEGIN\r\nRETURN dateadd(ms,-3,dateadd(dd, datediff(dd,0, @TheDate)+1,0))\r\nEND\r\nGO\r\n\r\n-- First day of the week\r\nCREATE FUNCTION dbo.DateWeekStarts(@TheDate DATETIME)\r\nRETURNS DATETIME AS\r\nBEGIN\r\nRETURN dateadd(ww, datediff(ww,0, @TheDate),0)\r\nEND\r\nGO\r\n\r\n-- Last day of the week, 3 ms to midnight\r\nCREATE FUNCTION dbo.DateWeekEnds(@TheDate DATETIME)\r\nRETURNS DATETIME AS\r\nBEGIN\r\nRETURN dateadd(ms,-3,dateadd(ww, datediff(ww,0, @TheDate)+1,0))\r\nEND\r\nGO\r\n\r\n-- First day of the month\r\nCREATE FUNCTION dbo.DateMonthStarts(@TheDate DATETIME)\r\nRETURNS DATETIME AS\r\nBEGIN\r\nRETURN dateadd(mm, datediff(mm,0, @TheDate),0)\r\nEND\r\nGO\r\n\r\n-- Last day of the month, 3 ms to midnight\r\nCREATE FUNCTION dbo.DateMonthEnds(@TheDate DATETIME)\r\nRETURNS DATETIME AS\r\nBEGIN\r\nRETURN dateadd(ms,-3,dateadd(mm, datediff(mm,0, @TheDate)+1,0))\r\nEND\r\nGO\r\n\r\n-- First day of the quarter\r\nCREATE FUNCTION dbo.DateQuarterStarts(@TheDate DATETIME)\r\nRETURNS DATETIME AS\r\nBEGIN\r\nRETURN dateadd(qq, datediff(qq,0, @TheDate),0)\r\nEND\r\nGO\r\n\r\n-- Last day of the quarter, 3 ms to midnight\r\nCREATE FUNCTION dbo.DateQuarterEnds(@TheDate DATETIME)\r\nRETURNS DATETIME AS\r\nBEGIN\r\nRETURN dateadd(ms,-3,dateadd(qq, datediff(qq,0, @TheDate)+1,0))\r\nEND\r\nGO\r\n\r\n-- First day of the year\r\nCREATE FUNCTION dbo.DateYearStarts(@TheDate DATETIME)\r\nRETURNS DATETIME AS\r\nBEGIN\r\nRETURN dateadd(yy, datediff(yy,0, @TheDate),0)\r\nEND\r\nGO\r\n\r\n-- Last day of the year, 3 ms to midnight\r\nCREATE FUNCTION dbo.DateYearEnds(@TheDate DATETIME)\r\nRETURNS DATETIME AS\r\nBEGIN\r\nRETURN dateadd(ms,-3,dateadd(yy, datediff(yy,0, @TheDate)+1,0))\r\nEND\r\nGO<\/pre>\n<\/p>\n","protected":false},"excerpt":{"rendered":"