Working with SQL Server Date/Time Variables - Page 3

April 16, 2003

Due to many variables in the format of input data for a date, the date may not always be a string of characters that is recognizes as a date by SQL Server. When you have this kind of raw date data, you will need to manipulate the input data to look like a date, before inserting it into a date/time column. A good example of a date format that is not recognized by SQL Server is an 8 digit date in MMDDYYYY format, this 8 character date format is not recognized by SQL Server as a date. In order to get the MMDDYYYY date into SQL Server you will need to convert it to a format that SQL Server knows is a date, like 'MM-DD-YYYY'. Let's look at ways to convert date/time data into a format that SQL Server can recognize.

As mentioned above a string in MMDDYYYY format, like '10301956' would not be accepted by SQL Server as a date. Therefore, if your input date was in this format you would need to stuff a couple of dashes into your data to make SQL Server recognize this as a date. There are a number of ways to do this. Here is some code that shows two different ways, there are many more. The first example uses the STUFF function to insert dashes in the appropriate place within the input date, while the second example uses the SUBSTRING and concatination ('+') function.

SET NOCOUNT ON
CREATE TABLE Y(D DATETIME)
DECLARE @INPUT CHAR(8)
SET @INPUT = '10301956'
-- FIRST EXAMPLE
INSERT INTO Y VALUES(STUFF(STUFF(@INPUT,3,0,'-'),6,0,'-'))
SELECT * FROM Y
TRUNCATE TABLE Y
-- SECOND EXAMPLE
INSERT INTO Y VALUES(SUBSTRING(@INPUT,1,2) + '-' + 
                     SUBSTRING(@INPUT,3,2) + '-' + 
                     SUBSTRING(@INPUT,5,4))
SELECT * FROM Y	
DROP TABLE Y

Depending on your input date format, normally the SUBSTRING, STUFF and concatenation functions are about all you need to reformat an input date into a format that SQL Server will except.

Another way to tell SQL Server how to interpret a string as a date is to use the SET DATEFORMAT command. This command allows you to input strings that would normally not be recognized by SQL server as dates. The SET DATEFORMAT command lets you specify what part of the date is first, second or third.

The format for the SET DATEFORMAT command is: SET DATEFORMAT <format> where <format> is one of the following: mdy, dmy, ymd, ydm, myd, or dym. The default DATEFORMAT is mdy. Let's looks a few examples on how the SET DATEFORMAT will allow you to specify the format of your input date.

SET NOCOUNT ON 
CREATE TABLE X(EXAMPLE INT, D SMALLDATETIME)
-- EXAMPLE 1
SET DATEFORMAT MDY
INSERT INTO X VALUES (1, '10/30/56')
-- EXAMPLE 2
SET DATEFORMAT YDM
INSERT INTO X VALUES (2, '56/31/10')
-- EXAMPLE 3 
SET DATEFORMAT YMD
INSERT INTO X VALUES (3, '56/10/31')
SELECT * FROM X
DROP TABLE X

In the above example, note how I changed the date format between each INSERT statement. The "SET DATEFORMAT" allowed me to INSERT dates with three different formats without doing any special data conversions.

Up to this point I have only been describing how to input the date portion of a date/time variable, there is still the time portion to consider. It is highly likely that you will be working with data that needs to be stored in SQL Server that has a time portion. One example of this might be converting data from a legacy system that stored the date and time in two different fields, where the date is in YYYYMMDD format, and the time is HHMMSSMMM or some other format.

Just as SQL Server accepts dates that meet a specific format, SQL Server also has some formats that it automatically expects for strings that contain times. Here is some information about the formats for time that SQL Server will accept. This information can be found in SQL Server 2000 Books Online.

Microsoft® SQL Server™ 2000 recognizes the following formats for time data. Enclose each format with single quotation marks (').

14:30
14:30[:20:999]
14:30[:20.9]
4am
4 PM
[0]4[:30:20:500]AM

You can specify a suffix of AM or PM to indicate if the time value is before or after 12 noon. The case of AM or PM is ignored.

Hours can be specified using either a 12-hour or 24-hour clock. This is how the hour values are interpreted:

• The hour value of 0 represents the hour after midnight (AM), regardless of whether or not you specify AM. You cannot specify PM when the hour equals 0.
• Hour values from 1 through 11 represent the hours before noon if neither AM nor PM is specified. They also represent the hours before noon when AM is specified. They represent hours after noon if PM is specified.
• The hour value 12 represents the hour that starts at noon if neither AM nor PM is specified. If AM is specified, it represents the hour that starts at midnight. If PM is specified, it represents the hour that starts at noon. For example: 12:01 is 1 minute after noon, as is 12:01 PM, while 12:01 AM is 1 minute after midnight. Specifying 12:01 AM is the same as specifying 00:01 or 00:01 AM.
• Hour values from 13 through 23 represents hours after noon if AM or PM is specified. They also represent the hours after noon when PM is specified. You cannot specify AM when the hour value is from 13 through 23.
• An hour value of 24 is not valid, use 12:00 AM or 00:00 to represent midnight.

Milliseconds can be preceded by either a colon (:) or a period (.). If preceded by a colon, the number means thousandths-of-a-second. If preceded by a period, a single digit means tenths-of-a-second, two digits mean hundredths-of-a-second, and three digits mean thousandths-of-a-second. For example, 12:30:20:1 indicates twenty and one-thousandth seconds past 12:30; 12:30:20.1 indicates twenty and one-tenth