Date and time function tutorials and examples in MSSQL |
>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. |
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
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
--dateadd(dd,0, datediff(dd, 0, dateField))
SELECT * FROM viewTrkTrackers WHERE
dateadd(dd,0, datediff(dd,0,closeDate)) = '04-30-2008'
SELECT * FROM viewTrkTrackers WHERE
(dateadd(dd,0, datediff(dd,0,closeDate)) >= (dateadd(dd,0, datediff(dd,0, (dateadd(dd, -10, getdate()))))))
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)
SELECT CONVERT(nvarchar(10), CONVERT(nvarchar(10), CONVERT(nvarchar(2), DATEPART(mm, GETDATE())) + '/01/' + CONVERT(nvarchar(4), DATEPART(yy, GETDATE()))), 101)
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