Date and time function tutorials and examples in MSSQL

Post Content

Summary

Brief Tutorial

Return Date with 00:00:00 time

Get the last day of previous month

Get the first day of this month

Get first/last example

Summary[-][--][++]

This topic contains a few of the MSSQL date functions and some useful snippets. Dates can get a little confusing in SQL but a good knowledge of their functions is critical for most data queries. Since the date manipulation queries are quite large, its best to set a @variable and reference that variable throughout the code.

Brief Tutorial[-][--][++]

Remember that the SQL data type has hours, minutes, seconds and sometimes milliseconds also, so you must account for that if needed.

To print todays date including the time SELECT getdate()
To print yesterdays date including the time SELECT dateadd(dd, -1, getdate())
To print date of 2 months ago including the time SELECT dateadd(mm, -2, getdate())
To print last years date including the time SELECT dateadd(yy, -1, getdate())

To print todays date but set time to 00:00:00.000 SELECT dateadd(dd, 0, datediff(dd,0, getdate()))
To print yesterdays date but set time to 00:00:00.000 SELECT dateadd(dd, -1, datediff(dd,0, getdate()))

or
To reset hours to 00:00:00 you can also use CONVERT(VARCHAR, datedate(),7) note this will display like 'May 21, 11' but when used in a WHERE date > Convert... will ignore hours correctly

So to select every records in a table for a static date we could do it the hard way with:
SELECT * FROM sometable WHERE somedate BETWEEN '2010-06-01 00:00:00.00' AND '2010-06-01 23:59:59.999'

Or we could just convert the somedate column to NO time first (the easy way)
SELECT * FROM sometable WHERE dateadd(dd, 0, datediff(dd, 0, somedate)) = '2010-06-01' or = '2010-06-01 00:00:00.00' it assumes 00's

If we wanted a rolling query, to pull every records from yesterday:
SELECT * FROM sometable WHERE dateadd(dd, 0, datediff(dd, 0, somedate)) = dateadd(dd, -1, datediff(dd,0, getdate()))

To get the first day of this month, with current time SELECT DATEADD(dd, -(DAY(getdate())-1), getdate())
To get the first day of this month, with time set to 00 SELECT DATEADD(dd, -(DAY(getdate())-1), DATEADD(dd, 0, DATEDIFF(dd, 0, getdate())))

To get the first day of last month, with current time SELECT DATEADD(dd, -(DAY(getdate())-1), DATEADD(mm, -1, getdate()))
To get the first day of last month, with time set to 00 SELECT DATEADD(dd, -(DAY(getdate())-1), DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(mm, -1, getdate()))))

To get the end of this month, with current time SELECT DATEADD(dd, -DAY(getdate()), DATEADD(mm, 1, getdate()))
To get the end of this month, with time set to 00 SELECT DATEADD(dd, -DAY(getdate()), DATEADD(mm, 1, DATEADD(dd, 0, DATEDIFF(dd, 0, getdate()))))
To get the end of this month, with time set to 23:59:59:997 SELECT DATEADD(dd, -DAY(getdate()-1), DATEADD(mm, 1, DATEADD(ms, -2, DATEADD(dd, 0, DATEDIFF(dd, 0, getdate())))))

To get the end of last month, with time set to 23:59:59:997 SELECT DATEADD(dd, -DAY(getdate()-1), DATEADD(mm, 1, DATEADD(ms, -2, DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(mm, -1, getdate()))))))

To select every records for last month:

Last Months Data

DECLARE @last_month_start datetime, @last_month_end datetime
SET @last_month_start = DATEADD(dd, -(DAY(getdate())-1), DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(mm, -1, getdate()))))
SET @last_month_end = DATEADD(dd, -DAY(getdate()-1), DATEADD(mm, 1, DATEADD(ms, -2, DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(mm, -1, getdate()))))))
SELECT * FROM sometable WHERE somedate BETWEEN @last_month_start AND @last_month_end

To find last week
http://www.objectreference.net/post/SQL-Find-last-week-date-range.aspx

DECLARE
    @now as datetime,
    @todayDayOfWeek as int,
    @startOfPrevWeek as datetime,
    @endOfPrevWeek as datetime

SET @now = getdate()
--SET @now = '2011-05-26'

--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)
SET @todayDayOfWeek = datepart(dw, @now)

--get this last Sunday (hours will be 00:00:00)
SET @endOfPrevWeek = DATEADD(dd, -@todayDayOfWeek+1, @now)
SET @endOfPrevWeek = DATEADD(dd, 0, DATEDIFF(dd,0, @endOfPrevWeek))

--get this Sunday before last
SET @startOfPrevWeek = DATEADD(dd, -7, @endOfPrevWeek)

--Example query for entire last week (Sunday 00:00:00 - Saturday 23:59:59)
--Note @endOfPrevWeek is actually Sunday 00:00:00 so if we use < then we actually get Saturday 23:59:59
--SELECT * WHERE datecolumn >= @startOfPrevWeek AND datecolumn < @endOfPrevWeek

Return Date with 00:00:00 time[-][--][++]

Date fields are usually yyyy-mm-dd hh:mm:ss.mil
To retrieve everything for today you could do a >= yyyy-mm-dd 00:00:00 and <= yyyy-mm-dd 23:59:59 (or use between)
But sometimes you need to ignore the hh:mm:ss.mil all together

--dateadd(dd,0, datediff(dd, 0, dateField))

SELECT * FROM viewTrkTrackers WHERE
    dateadd(dd,0, datediff(dd,0,closeDate)) = '04-30-2008'

This will show everything 10 days back, regardless of time

SELECT * FROM viewTrkTrackers WHERE
    (dateadd(dd,0, datediff(dd,0,closeDate)) >= (dateadd(dd,0, datediff(dd,0, (dateadd(dd, -10, getdate()))))))

Get the last day of previous month[-][--][++]

Return as nvarchar of mm/dd/yyyy

DECLARE @startDate as nvarchar(10)
--@startDate = the last day of last month (if today is 3/7 or 3/25... @startDate will be 2/29)...
SET @startDate = CONVERT(nvarchar(10), DATEADD(dd, -1, CONVERT(nvarchar(10), CONVERT(nvarchar(2), DATEPART(mm, @nowDate)) + '/01/' + CONVERT(nvarchar(4), DATEPART(yy, @nowDate)))), 101)

Get the first day of this month[-][--][++]

Return is nvarchar of mm/dd/yyyy

SELECT CONVERT(nvarchar(10), CONVERT(nvarchar(10), CONVERT(nvarchar(2), DATEPART(mm, GETDATE())) + '/01/' + CONVERT(nvarchar(4), DATEPART(yy, GETDATE()))), 101)

Get first/last example[-][--][++]

DECLARE 
    @startDate as smalldatetime,
    @endDate as smalldatetime,
    @startThisMonth as nvarchar(10),
    @startLastMonth as nvarchar(10),
    @endLastMonth as nvarchar(10)

SET @startThisMonth = CONVERT(nvarchar(10), CONVERT(nvarchar(10), CONVERT(nvarchar(2), DATEPART(mm, GETDATE())) + '/01/' + CONVERT(nvarchar(4), DATEPART(yy, GETDATE()))), 101)
SET @startLastMonth = CONVERT(nvarchar(10), CONVERT(nvarchar(10), CONVERT(nvarchar(2), DATEPART(mm, DATEADD(mm, -1, GETDATE()))) + '/01/' + CONVERT(nvarchar(4), DATEPART(yy, GETDATE()))), 101)
SET @endLastMonth = CONVERT(nvarchar(10), DATEADD(dd, -1, CONVERT(nvarchar(10), CONVERT(nvarchar(2), DATEPART(mm, GETDATE())) + '/01/' + CONVERT(nvarchar(4), DATEPART(yy, GETDATE())))), 101)

--Add startDate/endDate logic here later

SET @startDate = CONVERT(smalldatetime, @startLastMonth) + '00:00:00'
SET @endDate = CONVERT(smalldatetime, @endLastMonth) + '23:59:59'
SELECT @startDate
SELECT @endDate

--So use start >= @startDate and end < @endDate

produces
2008-04-01 00:00:00
2008-05-01 00:00:00

MSSQL Date Functions