This is a showcase review for our sponsors at CodeProject. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers.
Introduction
I don't think I'm in a minority of people when I confess that I learn programming skills best by trying things out! As such this "workbench", on the use of dates and times in SQL Server is a bit of a departure from the "usual" articles you'll find on Simple-Talk. It is designed so that it can be pasted in its entirety into the Query Analyser, SSMS or other GUI and the individual examples executed.
This "workbench" on use of Dates and Times in SQL is structured so it can be pasted in its entirity into the Query Analyser, SSMS or other GUI and the individual examples executed. I don't think I'm in a minority of people when I confess that I learn programming skills best by trying things out!
It provides working examples that tackle all of the common date-related requirements and problems, including:
- Inputting and Outputting dates
- Using ISDATE to check for valid dates
- Extracting parts of a date
- Finding the difference between two dates
- Formatting Dates
- Calculating dates (e.g. Start of the month)
- Practical date usage (e.g. calculating a daily total)
It also tackles some of the more tricky problems such as converting a UNIX Timestamp to a DateTime and finding DATETIME
of the start of the week.
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
- Inputting Times
- Outputting Dates
- Manipulating Dates
- Formatting Dates
- Calculating Dates
- Date Conversions
- Using Dates
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)
SET language us_english
SELECT CAST('14/2/2006' AS datetime)
SET dateformat 'dmy'
SELECT CAST('14/2/2006' AS datetime)
SET language british SELECT CAST('14/2/2006' AS datetime)
SET language us_english
SELECT CAST('14/2/2006' AS datetime)
So any date representation based on words (e.g. febbraio, fevereiro, february) will fail in any other language that uses a different word for the 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)
SET language british
SELECT CAST('1 feb 2006' AS datetime)
SELECT CAST('1 february 2006' AS datetime)
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)
SELECT CAST('2006-02-01T00:00:00' AS datetime)
SELECT CAST('2006-02-01T00:00:00.000' AS datetime)
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)
SELECT CONVERT(DateTime,'25/2/2006',100)
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 data 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)
SELECT CAST ('13:20:25:850' AS datetime)
SELECT CAST ('14:30:20.9' AS datetime)
SELECT CAST ('3am' AS datetime)
SELECT CAST ('10 PM' AS datetime)
SELECT CAST ('02:50:20:500AM' AS datetime)
SELECT CONVERT (DateTime,'02:50:20',108)
SELECT CONVERT(VARCHAR(20),GETDATE(),108)
SELECT LTRIM(RIGHT(CONVERT(CHAR(19),GETDATE(),100),7))
SELECT LTRIM(RIGHT(CONVERT(CHAR(26),GETDATE(),109),14))
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)
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
INSERT INTO @types
SELECT NULL,100,'Default'
INSERT INTO @types
SELECT 1,101, 'USA'
INSERT INTO @types
SELECT 2,102, 'ANSI'
INSERT INTO @types
SELECT 3,103, 'British/French'
INSERT INTO @types
SELECT 4,104, 'German'
INSERT INTO @types
SELECT 5,105, 'Italian'
INSERT INTO @types
SELECT 6,106, 'dd mon yy'
INSERT INTO @types
SELECT 7,107, 'Mon dd, yy'
INSERT INTO @types
SELECT 8,108, 'hh:mm:ss'
INSERT INTO @types
SELECT NULL,109, 'Default + milliseconds'
INSERT INTO @types
SELECT 10,110,'USA'
INSERT INTO @types
SELECT 11,111,'JAPAN'
INSERT INTO @types
SELECT 12,112,'ISO'
INSERT INTO @types
SELECT NULL,113,'Europe default(24h) + milliseconds'
INSERT INTO @types
SELECT 14,114,'hh:mi:ss:mmm (24h)'
INSERT INTO @types
SELECT NULL,120,'ODBC canonical (24h)'
INSERT INTO @types
SELECT NULL,121, 'ODBC canonical (24h)+ milliseconds'
INSERT INTO @types
SELECT NULL,126, 'ISO8601'
INSERT INTO @types
SELECT NULL,130, 'Hijri'
INSERT INTO @types
SELECT NULL,131, 'Hijri'
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()
SELECT GETUTCDATE()
ELECT CURRENT_TIMESTAMP
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
- 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.000', DATEADD(YEAR,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(quarteer,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(MONTH,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000',
DATEADD(dayofyear,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(DAY,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(week,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(weekday,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(hour,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(minute,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000',
DATEADD(second ,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000',
DATEADD(millisecond,100,'2007-01-01 00:00:00.000')
getting the current date can be done by three functions
SELECT GETDATE()
SELECT GETUTCDATE()
SELECT CURRENT_TIMESTAMP
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')
SELECT DATEDIFF(DAY,'1 feb 2008','1 mar 2008')
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 millisecond within the date. The Month and weekday are given in full from the value in the sysLanguages table.
SELECT DATENAME (YEAR,GETDATE())
SELECT DATENAME (quarter,GETDATE())
SELECT DATENAME (MONTH,GETDATE())
SELECT DATENAME (dayofyear,GETDATE())
SELECT DATENAME (DAY,GETDATE())
SELECT DATENAME (week,GETDATE())
SELECT DATENAME (weekday,GETDATE())
SELECT DATENAME (hour,GETDATE())
SELECT DATENAME (minute,GETDATE())
SELECT DATENAME (second ,GETDATE())
SELECT DATENAME (millisecond,GETDATE())
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())
SELECT DATEPART(quarter,GETDATE())
SELECT DATEPART(MONTH,GETDATE())
SELECT DATEPART(dayofyear,GETDATE())
SELECT DATEPART(DAY,GETDATE())
SELECT DATEPART(week,GETDATE())
SELECT DATEPART(weekday,GETDATE())
SELECT DATEPART(hour,GETDATE())
SELECT DATEPART(minute,GETDATE())
SELECT DATEPART(second ,GETDATE())
SELECT DATEPART(millisecond,GETDATE())
Formatting Dates
Examples of calculating and formatting dates
SELECT DATENAME(dw,GETDATE())
SELECT LEFT(DATENAME(dw,GETDATE()),3)
SELECT DATEPART(dw,GETDATE())+(((@@Datefirst+3)%7)-4)
SELECT RIGHT('00' + CAST(DAY(GETDATE()) AS VARCHAR),2)
SELECT CAST(DAY(GETDATE()) AS VARCHAR)
SELECT DATEPART(dy,GETDATE())
SELECT DATEPART(week,GETDATE())
SET datefirst 1 SELECT DATEPART(week,GETDATE())
SELECT DATENAME(MONTH,GETDATE())
SELECT LEFT(DATENAME(MONTH,GETDATE()),3)
SELECT RIGHT('00' + CAST(MONTH(GETDATE()) AS VARCHAR),2)
SELECT RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR),2)
SELECT CAST(YEAR(GETDATE()) AS VARCHAR)
SELECT DATEPART(hour,GETDATE())
SELECT LEFT(RIGHT(CONVERT(CHAR(19),GETDATE(),100),7),2)
SELECT DATEPART(minute,GETDATE())
SELECT DATEPART(second,GETDATE())
SELECT RIGHT(CONVERT(CHAR(19),GETDATE(),100),2)
SELECT CONVERT(VARCHAR(8),GETDATE(),8)
SELECT RIGHT(CONVERT(CHAR(19),GETDATE(),100),7)
SELECT DATEDIFF(hour, GETDATE(), GETUTCDATE())
SELECT SUBSTRING('stndrdthththththththththththththththththstndrdthththththththst'
,(DATEPART(DAY,GETDATE())*2)-1,2)
SELECT DATENAME(dw,GETDATE())+', '+ STUFF(CONVERT(CHAR(11),GETDATE(),106),3,0,
SUBSTRING('stndrdthththththththththththththththththstndrdthththththththst'
,(DATEPART(DAY,GETDATE())*2)-1,2))
Calculating Dates by example
SELECT GETDATE()
SELECT CAST(CONVERT(CHAR(11),GETDATE(),113) AS datetime)
SELECT CAST(CONVERT(CHAR(11),DATEADD(DAY,1,GETDATE()),113) AS datetime)
SELECT CAST(CONVERT(CHAR(11),DATEADD(DAY,-1,GETDATE()),113) AS datetime)
SELECT DATEADD(DAY,((7-DATEPART(dw,GETDATE())+(((@@Datefirst+3)%7)+2)) % 7),
GETDATE())
SELECT DATEADD(DAY,-((7-DATEPART(dw,GETDATE())+(((@@Datefirst+3)%7)+3)) % 7),
GETDATE())
SELECT DATEADD(hour,2,GETDATE())
SELECT DATEADD(hour,-2,GETDATE())
SELECT DATEADD(MONTH,-1,GETDATE())
SELECT CAST('01 '+ RIGHT(CONVERT(CHAR(11),GETDATE(),113),8) AS datetime)
SELECT CAST('01 ' + RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,-1,GETDATE()),113),8)
AS datetime)
SELECT CAST('01 '+ RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,1,GETDATE()),113),8)
AS datetime)
SELECT DATEADD(minute,-10,GETDATE())
SELECT CAST(CONVERT(CHAR(11),GETDATE(),113) AS datetime)
SELECT CAST(CONVERT(CHAR(11),DATEADD(DAY,1,GETDATE()),113) AS datetime)
SELECT DATEADD(week,-3,GETDATE())
SELECT DATEADD(DAY, -(DATEPART(dw,GETDATE())-1),GETDATE())
SELECT DATEADD(YEAR,-1,GETDATE())
SELECT CAST('01 Jan'+ DATENAME(YEAR,GETDATE()) AS datetime)
SELECT CAST('01 Jan'+ DATENAME(YEAR,DATEADD(YEAR,-1,GETDATE())) AS datetime)
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())
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 HERE purchaseDate LIKE '%9:40%'
SELECT COUNT(*) FROM [purchases]
WHERE purchaseDate LIKE '%feb%'
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