/*
Using dates, and times in SQL Server: a workbench approach
This "workbench" on use of dates and times in SQL Server is structured so it can be pasted in its entirity into the Query Analyser, SSMS or other GUI and the individual examples executed.
I'd like to encourage you to experiment. One never fails to come up with surprises; for example, I'd never, before writing this, considered using 'LIKE' when searching DateTime fields, or using the { t '2:40'} in a stored procedure as a literal date.
Likewise, I always like to see as many examples as possible in any articles on SQL Server. There is nothing like it for getting ideas going. Formal descriptions are fine for those with strange lumps in their brains, but I'd prefer to see clear explanations peppered with examples!
If I have any general advice, it is to use the strengths of the DATETIME data type and never attempt to bypass its use, by storing dates or times in any other formats. I've never come across a circumstance where such a practice has provided any lasting benefit.
Contents
Inputting dates
---------------
A user enters a date into form and you need to get it into a DATETIME data type
in The Database. Dates can be assigned to DateTime variables or columns as strings
but these are done according to the dateformat stored for the particular language
that is current. The orderin which the month (m), day (d), and year (y) is written
is different in other countries. US_English (mdy) is different from british (dmy).
By explicitly setting the date format you can over-ride this.
You can check your current DateFormat, amongst other things by using... */
DBCC
USEROPTIONS
--now, to demonstrate that getting this wrong can cause unexpected errors.....
SET language british
SELECT CAST('14/2/2006' AS datetime) --2006-02-14 00:00:00.000
SET language us_english --Changed language setting to us_english.
SELECT CAST('14/2/2006' AS datetime) --**ERROR!***
--keep speaking American, but use the european date format
SET dateformat 'dmy' --to override the language default
SELECT CAST('14/2/2006' AS datetime) --2006-02-14 00:00:00.000
SET language british
SELECT CAST('14/2/2006' AS datetime) --2006-02-14 00:00:00.000
SET language us_english --Changed language setting to us_english.
SELECT CAST('14/2/2006' AS datetime) --2006-02-14 00:00:00.000
/* Any date representation based on words (e.g. febbraio, fevereiro, february)
will fail in any other language that uses a different word for a given month.
To see the current language settings, use: */
sp_HelpLanguage
/* To import foregn-language dates, you must change the language setting for the
connection.
e.g
*/
SET language Italiano SELECT CAST('10 febbraio 2006' AS datetime)
--Changed language setting to Italiano.
--2006-02-10 00:00:00.000
/* Otherwise SQL Server is fairly
accomodating, and will do its best to make sense of a date.*/
--all of the following return 2006-02-01 00:00:00.000
SET language british
SELECT CAST('1 feb 2006' AS datetime)--remember, this is language dependent
SELECT CAST('1 february 2006' AS datetime)--this too
SELECT CAST('01-02-06' AS datetime)
SELECT CAST('2006-02-01 00:00:00.000' AS datetime)
SELECT CAST('1/2/06' AS datetime)
SELECT CAST('1.2.06' AS datetime)
SELECT CAST('20060201' AS datetime)
--in SQL Server 2000 and 2005 you can specify dates in ISO 8601 format
SELECT CAST('2006-02-01T00:00:00' AS datetime)
SELECT CAST('2006-02-01T00:00:00.000' AS datetime)
--and you'll be able to enter in this format whatever the settings!
/* the ANSI standard date uses braces, the marker 'd' to designate the date,
and a date string */
SELECT { d '2006-02-01' }
/* the ANSI standard datetime uses 'ts' instead of 'd' and adds hours, minutes,
and seconds to the date (using a 24-hour clock) */
SELECT { ts '2006-02-01 00:00:00' }
/*
If you use the CONVERT function, you can override the dateformat by choosing
the correct CONVERT style (103 is the British/French format of dd/mm/yyyy
(see later for a list of all the styles)
*/
SET language us_english
SELECT CONVERT(DateTime,'25/2/2006',103) --works fine
--whereas the 100 style uses the default supplied by the dateformat.
SELECT CONVERT(DateTime,'25/2/2006',100) --error!
/*
The IsDate function
-------------------
The IsDate(expression) function is used for checking strings to see if they
are valid dates. It is language-dependent.
ISDATE (Expression) returns 1 if the expression is a valid date (according
to the language and dateformat mask) and 0 if it isn't
The following demonstration uses ISDATE to test out the input of strings as
dates. */
--
SET LANGUAGE british SET nocount ON
--
DECLARE @DateAsString VARCHAR(20),
@DateAsDateTime DateTime
SELECT @DateAsString='2 february 2002'
SELECT [input]=@DateAsString
IF (ISDATE(@DateAsString)=1)
BEGIN
SELECT @DateAsDateTime=@DateAsString
SELECT [the Date]=COALESCE(CONVERT(CHAR(17),@DateAsDateTime,113),'unrecognised')
END
ELSE
SELECT [the Date] ='That was not a date'
/*
Inputting Times
---------------
Times can be input into SQL Server just as easily. There are no separate time
and date types for storing only times or only dates. It is not necessary. If
only a time is specified when setting a datetime, the date is assumed to be
the first of january 1900, the year of the start of the new millenium.
If only a date is specified, the time defaults to Midnight.
e.g.
*/
SELECT CAST ('17:45' AS datetime) -- 1900-01-01 17:45:00.000
SELECT CAST ('13:20:25:850' AS datetime) -- 1900-01-01 13:20:25.850
SELECT CAST ('14:30:20.9' AS datetime) -- 1900-01-01 14:30:20.900
SELECT CAST ('3am' AS datetime) -- 1900-01-01 03:00:00.000
SELECT CAST ('10 PM' AS datetime) -- 1900-01-01 22:00:00.000
SELECT CAST ('02:50:20:500AM' AS datetime) -- 1900-01-01 02:50:20.500
SELECT CONVERT (DateTime,'02:50:20',108) -- 1900-01-01 02:50:20.000
-- And times can be converted back from the DATETIME into the ascii VARCHAR
-- version as follows...
SELECT CONVERT(VARCHAR(20),GETDATE(),108) -- 15:08:52
--108 is the hh:mm:ss CONVERT style (See next section for the complete list)
SELECT LTRIM(RIGHT(CONVERT(CHAR(19),GETDATE(),100),7))-- 3:10PM
SELECT LTRIM(RIGHT(CONVERT(CHAR(26),GETDATE(),109),14)) -- 3:19:18:810PM
-- and so on
-- You can input times a different way (note that the brackets are curly
-- braces
SELECT { t '09:40:00' }
-- which unexpectedly gives 09.40 today, rather than 9:40 on the first of
-- january 1900! (as one might expect from the other time input examples)
-- this is valid in a stored procedure too
CREATE PROCEDURE #spExperiment AS
SELECT { t '09:40:00' }
GO
EXEC #spExperiment
/*
Outputting dates
----------------
Dates can be output as strings in a number of ways using the CONVERT function
and CONVERT styles These styles are numeric codes that correspond with the
most popular date formats. You get much more versatility with the CONVERT
function than the CAST function.
The CONVERT styles override the setting of the DATEFORMAT but use the
current language setting where the date format uses the name of the month.
If you run the following code you will get a result that illustrates all the
built-in formats , using the current date and time
--------------------------------------------------------------*/
DECLARE @types TABLE(
[2 digit year] INT NULL,
[4 digit year] INT NOT NULL,
name VARCHAR(40))
SET LANGUAGE british SET nocount ON
--Each select statement is followed by an example output string using the style
INSERT INTO @types
SELECT NULL,100,'Default'--Oct 17 2006 9:29PM
INSERT INTO @types
SELECT 1,101, 'USA'--10/17/06 or 10/17/2006
INSERT INTO @types
SELECT 2,102, 'ANSI'--06.10.17 or 2006.10.17
INSERT INTO @types
SELECT 3,103, 'British/French'--17/10/06 or 17/10/2006
INSERT INTO @types
SELECT 4,104, 'German'--17.10.06 or 17.10.2006
INSERT INTO @types
SELECT 5,105, 'Italian'--17-10-06 or 17-10-2006
INSERT INTO @types
SELECT 6,106, 'dd mon yy'--17 Oct 06 or 17 Oct 2006
INSERT INTO @types
SELECT 7,107, 'Mon dd, yy'--Oct 17, 06 or Oct 17, 2006
INSERT INTO @types
SELECT 8,108, 'hh:mm:ss' --21:29:45 or 21:29:45
INSERT INTO @types
SELECT NULL,109, 'Default + milliseconds'--Oct 17 2006 9:29:45:500PM
INSERT INTO @types
SELECT 10,110,'USA' --10-17-06 or 10-17-2006
INSERT INTO @types
SELECT 11,111,'JAPAN'--06/10/17 or 2006/10/17
INSERT INTO @types
SELECT 12,112,'ISO'--061017 or 20061017
INSERT INTO @types --17 Oct 2006 21:29:45:500
SELECT NULL,113,'Europe default(24h) + milliseconds'
INSERT INTO @types
SELECT 14,114,'hh:mi:ss:mmm (24h)' --21:29:45:500 or 21:29:45:500
INSERT INTO @types
SELECT NULL,120,'ODBC canonical (24h)'--2006-10-17 21:29:45
INSERT INTO @types --2006-10-17 21:29:45.500
SELECT NULL,121, 'ODBC canonical (24h)+ milliseconds'
INSERT INTO @types
SELECT NULL,126, 'ISO8601'--2006-10-17T21:29:45.500
-- insert into @types
-- Select null,127, 'ISO8601 with time zone' --SQL Server 2005 only!
INSERT INTO @types
SELECT NULL,130, 'Hijri'--25 ????? 1427 9:33:21:340PM
INSERT INTO @types
SELECT NULL,131, 'Hijri'--25/09/1427 9:29:45:500PM
SELECT [name],
[2 digit year]=COALESCE(CONVERT(VARCHAR(3),[2 digit year]),'-'),
[example]=CASE WHEN [2 digit year] IS NOT NULL
THEN CONVERT(VARCHAR(30),GETDATE(),[2 digit year])
ELSE '-' END,
[4 digit year]=COALESCE(CONVERT(VARCHAR(3),[4 digit year]),'-'),
[example]=CASE WHEN [4 digit year] IS NOT NULL
THEN CONVERT(VARCHAR(30),GETDATE(),[4 digit year])
ELSE '-' END
FROM @types
--------------------------------------------------------------------------
/*
Manipulating dates
------------------
Getting the CURRENT date can be done BY three functions: */
SELECT GETDATE() --the local date and time
SELECT GETUTCDATE() --the UTC or GMT date and time
SELECT CURRENT_TIMESTAMP--synonymous with GetDate()
-- When extracting parts of a DateTime you have some handy functions that
-- return integers
-- DAY, MONTH, YEAR .. here we get the day, month and year as integers
SELECT DAY(GETDATE()),MONTH(GETDATE()),YEAR(GETDATE())
-- The functions DAY MONTH AND YEAR are shorthand for the equivalent
-- DATEPART command, but for more general use the DATEPART function
-- is more versatile
SELECT DATEPART(DAY,GETDATE()),DATEPART(MONTH,GETDATE()),
DATEPART(YEAR,GETDATE())
/*
DATEADD
-------
DATEADD will actually add a number of years, quarters, months,weeks,days,
hours, minutes, seconds, or milliseconds to your specifced date. The format is as follows:
year (yy or yyyy)
quarter (qq or q)
month (mm or m)
week (wk or ww)
Day (dayofyear, dy, y, day, dd, d, weekday or dw)
hour (hh
minute (mi or n),
second (ss or s)
millisecond (ms)
In these examples we compare the date with the DATEADDed date so you can see
the effect that the DATEADD is having to it*/
--
SELECT '2007-01-01 00:00:00', DATEADD(YEAR,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00', DATEADD(quarter,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00', DATEADD(MONTH,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00', DATEADD(dayofyear,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00', DATEADD(DAY,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00', DATEADD(week,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00', DATEADD(weekday,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00', DATEADD(hour,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00', DATEADD(minute,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00', DATEADD(second ,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00', DATEADD(millisecond,100,'2007-01-01 00:00:00.000')
--getting the current date can be done by three functions
SELECT GETDATE() --the loval date and time
SELECT GETUTCDATE() --the UTC or GMT date and time
SELECT CURRENT_TIMESTAMP--synonymous with GetDate()
/*
DATEDIFF
--------
DATEDIFF returns an integer of the difference between two dates expressed in Years,
quarters, Months,Weeks,Days,Hours,minutes,seconds or milliseconds (it counts the
boundaries).*/
SELECT DATEDIFF(DAY,'1 feb 2006','1 mar 2006')--28
SELECT DATEDIFF(DAY,'1 feb 2008','1 mar 2008')--29. Hmm must be a leap year!
/*
We will give some practical examples of its use later on in the workshop
DATENAME
--------
Unlike DatePart, which returns an integer, DATENAME returns a NVarchar
representing the Year,quarter,Month,Week,day of the week,Day of the year,
Hour,minute, second or illisecond within the date. The Month and weekday
are given in full from the value in the sysLanguages table.
*/
SELECT DATENAME (YEAR,GETDATE()) --2006
SELECT DATENAME (quarter,GETDATE()) --4
SELECT DATENAME (MONTH,GETDATE()) --October
SELECT DATENAME (dayofyear,GETDATE()) --285
SELECT DATENAME (DAY,GETDATE()) --12
SELECT DATENAME (week,GETDATE()) --42
SELECT DATENAME (weekday,GETDATE()) --Thursday
SELECT DATENAME (hour,GETDATE()) --9
SELECT DATENAME (minute,GETDATE()) --32
SELECT DATENAME (second ,GETDATE()) --8
SELECT DATENAME (millisecond,GETDATE()) --875
/*
DATEPART
--------
DATEPART returns an integer representing the part of the date requested in the 1st
parameter. You can use year (yy or yyyy), quarter (qq or q), month (mm or m),
dayofyear (dy or y) day (dd or d), week (wk or ww) , weekday (dw),hour (hh),
minute (mi or n), second (ss or s), or millisecond (ms) */
SELECT DATEPART(YEAR,GETDATE()) --2006
SELECT DATEPART(quarter,GETDATE()) --4
SELECT DATEPART(MONTH,GETDATE()) --10
SELECT DATEPART(dayofyear,GETDATE()) --285
SELECT DATEPART(DAY,GETDATE()) --12
SELECT DATEPART(week,GETDATE()) --42
SELECT DATEPART(weekday,GETDATE()) --4
SELECT DATEPART(hour,GETDATE()) --9
SELECT DATEPART(minute,GETDATE()) --32
SELECT DATEPART(second ,GETDATE()) --8
SELECT DATEPART(millisecond,GETDATE()) --875
/*
Formatting Dates
-----------------
Examples of calculating and formatting dates
*/
--To get the full Weekday name
SELECT DATENAME(dw,GETDATE())
--To get the abbreviated Weekday name (MON, TUE, WED etc)
SELECT LEFT(DATENAME(dw,GETDATE()),3)
--ISO-8601 Weekday number
SELECT DATEPART(dw,GETDATE())+(((@@Datefirst+3)%7)-4)
--Day of the month with leading zeros
SELECT RIGHT('00' + CAST(DAY(GETDATE()) AS VARCHAR),2)
--Day of the month without leading space
SELECT CAST(DAY(GETDATE()) AS VARCHAR)
--day of the year
SELECT DATEPART(dy,GETDATE())
--number of the week in the year
SELECT DATEPART(week,GETDATE())
--ISO-8601 number of the week of the year (monday as the first day of the week)
SET datefirst 1 SELECT DATEPART(week,GETDATE())
--you may need to preserve and restore the value
--full name of the month
SELECT DATENAME(MONTH,GETDATE())
--Abbreviated name of the month
SELECT LEFT(DATENAME(MONTH,GETDATE()),3)--not true of finnish or french!
--Number of the month with leading zeros
SELECT RIGHT('00' + CAST(MONTH(GETDATE()) AS VARCHAR),2)
--two-digit year
SELECT RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR),2)
--four-digit year
SELECT CAST(YEAR(GETDATE()) AS VARCHAR)
--hour (00-23)
SELECT DATEPART(hour,GETDATE())
--Hour (01-12)
SELECT LEFT(RIGHT(CONVERT(CHAR(19),GETDATE(),100),7),2)
--minute
SELECT DATEPART(minute,GETDATE())
--second
SELECT DATEPART(second,GETDATE())
--PM/AM indicator
SELECT RIGHT(CONVERT(CHAR(19),GETDATE(),100),2)
--time in 24 hour notation
SELECT CONVERT(VARCHAR(8),GETDATE(),8)
--Time in 12 hour notation
SELECT RIGHT(CONVERT(CHAR(19),GETDATE(),100),7)
--timezone (or daylight-saving)
SELECT DATEDIFF(hour, GETDATE(), GETUTCDATE())
----ordinal suffix for the date
SELECT SUBSTRING('stndrdthththththththththththththththththstndrdthththththththst'
,(DATEPART(DAY,GETDATE())*2)-1,2)
--full date (the variations are infinite. Here is one example
SELECT DATENAME(dw,GETDATE())+', '+ STUFF(CONVERT(CHAR(11),GETDATE(),106),3,0,
SUBSTRING('stndrdthththththththththththththththththstndrdthththththththst'
,(DATEPART(DAY,GETDATE())*2)-1,2))
--e.g. Thursday, 12th Oct 2006
/*
Calculating Dates by example
----------------------
*/
-- now
SELECT GETDATE()
-- Start of today (first thing)
SELECT CAST(CONVERT(CHAR(11),GETDATE(),113) AS datetime)
-- Start of tomorrow (first thing)
SELECT CAST(CONVERT(CHAR(11),DATEADD(DAY,1,GETDATE()),113) AS datetime)
-- Start of yesterday (first thing)
SELECT CAST(CONVERT(CHAR(11),DATEADD(DAY,-1,GETDATE()),113) AS datetime)
-- This time Next thursday (today if it is thursday)
SELECT DATEADD(DAY,((7-DATEPART(dw,GETDATE())+(((@@Datefirst+3)%7)+2)) % 7),GETDATE())
-- This time Last friday (today if it is friday)
SELECT DATEADD(DAY,-((7-DATEPART(dw,GETDATE())+(((@@Datefirst+3)%7)+3)) % 7),GETDATE())
-- Two hours time
SELECT DATEADD(hour,2,GETDATE())
-- Two hours ago
SELECT DATEADD(hour,-2,GETDATE())
-- Same date and time last month
SELECT DATEADD(MONTH,-1,GETDATE())
-- Start of the month
SELECT CAST('01 '+ RIGHT(CONVERT(CHAR(11),GETDATE(),113),8) AS datetime)
-- Start of last month
SELECT CAST('01 '+ RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,-1,GETDATE()),113),8) AS datetime)
-- Start of next month
SELECT CAST('01 '+ RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,1,GETDATE()),113),8) AS datetime)
-- Ten minutes ago
SELECT DATEADD(minute,-10,GETDATE())
-- Midnight last night
SELECT CAST(CONVERT(CHAR(11),GETDATE(),113) AS datetime)
-- Midnight tonight
SELECT CAST(CONVERT(CHAR(11),DATEADD(DAY,1,GETDATE()),113) AS datetime)
-- Three weeks ago
SELECT DATEADD(week,-3,GETDATE())
-- Start of the week (this depends on your @@DateFirst setting)
SELECT DATEADD(DAY, -(DATEPART(dw,GETDATE())-1),GETDATE())
-- last year
SELECT DATEADD(YEAR,-1,GETDATE())
-- new year, this year
SELECT CAST('01 Jan'+ DATENAME(YEAR,GETDATE()) AS datetime)
-- new year, last year
SELECT CAST('01 Jan'+ DATENAME(YEAR,DATEADD(YEAR,-1,GETDATE())) AS datetime)
-- next christmas
SELECT CASE WHEN DATEPART(dy,GETDATE())<DATEPART(dy,'25 Dec'+ + DATENAME(YEAR,GETDATE()))
THEN CAST('25 Dec'+ + DATENAME(YEAR,GETDATE()) AS datetime)
ELSE CAST('25 Dec'+ CAST(DATEPART(YEAR,GETDATE())+1 AS VARCHAR) AS datetime) END
/*
Date Conversions
-----------------
The DATETIME data type stores the Date and time data from January 1, 1753 to
December 31, 9999, to an accuracy of one 3.33 milliseconds. Values are rounded.
Values are stored as two 4-byte integers:
. The first 4 bytes store the number of days +- from the base date, January 1, 1900.
The base date is the system reference date.
. The second 4 bytes store the time of day represented as the number of milliseconds
after midnight.
Values for datetime earlier than January 1, 1753 are not permitted.
When converting from SQL Server dates to Unix timestamps, the dates are rounded to the
nearest second (Unix timestamps are only accurate to the nearest second)
SQL Server date to UNIX timestamp (based on seconds since standard epoch of 1/1/1970)
*/
SELECT DATEDIFF(second,'1/1/1970',GETDATE())
-- UNIX timestamp to SQL Server
SELECT DATEADD(second, 1160986544, '1/1/1970') /*
Using dates
-----------
When storing dates, always us the datetime data type. Do not feel tempted to use
tricks such as storing the year, month or day as integers, with the idea that this
will help retrieval and aggregation for reports. It never does. The manipulation of
datetimes is so critical to SQL Server's performance that it is highly optimised.
indexes based on DateTimes work very well, sort properly, and allow fast partitioning
on a variety of criteria such as week, month, year-to-date and so on.
If, for example, you store a list of purchases by date in a table such as PURCHASES
you can find the sum for the previous week by... */
SELECT SUM(total) FROM purchases
WHERE purchaseDate BETWEEN DATEADD(week,-1,GETDATE()) AND GETDATE()
--this will pick up an index on PurchaseDate
--what about sales since the start of the week
SELECT SUM(total) FROM purchases
WHERE purchaseDate BETWEEN
DATEADD(DAY, -(DATEPART(dw,GETDATE())-1),GETDATE()) AND GETDATE()
--Want a daily total?
SELECT CONVERT(CHAR(11),PurchaseDate,113),
SUM(total) FROM purchases
GROUP BY CONVERT(CHAR(11),PurchaseDate,113)
ORDER BY MIN(PurchaseDate)
--Or to find out which days of the week were the best?
SELECT DATENAME(dw,PurchaseDate),
[No. Purchases]=COUNT(*), [revenue]=SUM(total) FROM [purchases]
GROUP BY DATENAME(dw,PurchaseDate), DATEPART(dw,PurchaseDate)
ORDER BY DATEPART(dw,PurchaseDate)
--Want a week by week total?
SELECT 'Week '+DATENAME(week,purchaseDate)+' '+DATENAME(YEAR,purchaseDate),
SUM(total) FROM purchases
GROUP BY 'Week '+DATENAME(week,purchaseDate)+' '+DATENAME(YEAR,purchaseDate)
ORDER BY MIN(InsertionDate)
--(you'd miss weeks where nothing was purchased if you did it this way.)
/* The LIKE expression can be used for searching for datetime values.
If, for example, one wants to search for all purchases done at 9:40, one can find
a match by the clause WHERE purchaseDate LIKE '%9:40%'. */
SELECT * FROM [purchases]
WHERE purchaseDate LIKE '%9:40%'
--or all purchases in the month of february
SELECT COUNT(*) FROM [purchases]
WHERE purchaseDate LIKE '%feb%'
--all purchases where there is a 'Y' in the month (matches only May!)
SELECT DATENAME(MONTH, insertionDate), COUNT(*) FROM [purchases]
WHERE purchaseDate LIKE '%y%'
GROUP BY DATENAME(MONTH, purchaseDate)
/* this 'Like' trick is of limited use and should be used with considerable caution as
it uses artifice to get its results*/