This is the first article in a series of articles that I will discuss various aspects of working with SQL Server date/time columns. SQL Server has two different date/time columns. They are DATETIME and SMALLDATETIME. This article will define the difference between these two SQL Server date/time data types, as well as show you how to insert date and time data into SQL Server DATETIME and SMALLDATETIME columns.

The DATETIME column is used to hold a date and time value, where time is accurate to three-hundredth of a second. The date for a DATETIME column can range from January 1, 1753 to December 31, 9999. A DATETIME column takes 8 bytes of disk storage. The physical storage of these 8 bytes is divided into 2 - 4 byte integer pieces. The first 4 byte integer is used to store the number of days before or after the base date January 1, 1900, while the second 4 bytes integer is used to represent the number of milliseconds since midnight.

A SMALLDATETIME column also holds a date and time value, but the time portion is only accurate to one minute. Valid dates for a SMALLDATETIME column can range from January 1, 1900 to June 6, 2079. The SMALLDATETIME column takes 4 bytes of storage. This 4 bytes is broken into two 2 byte integer pieces. The first 2 bytes integer piece contains the number of days since January 1, 1900; the second 2 byte integer holds the number of minutes since midnight.

Most, if not all, applications need and manage date and time variables in their database. Since data from applications and external sources may come in many formats, you need to know how to insert these dispersant values into DATETIME and SMALLDATETIME columns.

SQL Server has a number of default formats it expects raw date and time data to be in when inserting dates into a DATETIME, or SMALLDATETIME columns. If you attempt to insert a date that does not match one of the default date formats, then SQL Server will reject the date/time value. If your raw data does match one of the default formats, SQL Server will automatically convert your raw data into a DATETIME value without any special consideration.

Let's review some TSQL code for inserting dates and times into SQL Server. Here is a sample script that inserts the same date 10/30/1956 into a sample table using a number of different string formats.

DROP TABLE X 

GO 

SET NOCOUNT ON

CREATE TABLE X(D DATETIME)



INSERT INTO X VALUES ('19561030')

INSERT INTO X VALUES ('561030')

INSERT INTO X VALUES ('10/30/1956')

INSERT INTO X VALUES ('10/30/56')

INSERT INTO X VALUES ('30 OCT 1956')

INSERT INTO X VALUES ('30 OCT 56')

INSERT INTO X VALUES ('OCT 30 1956')

INSERT INTO X VALUES ('OCT 30, 1956')

INSERT INTO X VALUES ('OCT 30, 56')

INSERT INTO X VALUES ('OCTOBER 10, 1956')

SELECT * FROM X

As you can see from this example, not all dates specified contain a 4 digit year. SQL Server has a two-digit year cutoff option that makes this possible. My SQL Server uses the default setting which is 2049. Meaning if the year looks to be from 00-49, SQL Server will assume the first two digits of the year will be 20. In my example, the year was greater than 49, so SQL Server set the year to 1956. The two digit year cutoff is a configurable option.

Here is what books online says about the acceptable aphabetic and numeric formats that are automatically recognized as dates by SQL Server:

Alphabetic Date Format

Microsoft. SQL Server™ 2000 allows you to specify date data with a month specified as the full month name (for example, April) or the month abbreviation (for example, Apr) given in the current language; commas are optional and capitalization (case) is ignored.

Here are some guidelines for the use of alphabetic date formats:

• Enclose the date and time data in single quotation marks (').
• These are the valid alphabetic formats for SQL Server date data (characters enclosed in brackets are optional):
• Apr[il] [15][,] 1996
• Apr[il] 15[,] [19]96
• Apr[il] 1996 [15]
• [15] Apr[il][,] 1996
• 15 Apr[il][,][19]96
• 15 [19]96 apr[il]
• [15] 1996 apr[il]
• 1996 APR[IL] [15]
• 1996 [15] APR[IL]
• If you specify only the last two digits of the year, values less than the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than or equal to the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2050 (default), 25 is interpreted as 2025 and 50 is interpreted as 1950. To avoid ambiguity, use four-digit years.
• If the day is missing, the first day of the month is supplied.
• The SET DATEFORMAT session setting is not applied when you specify the month in alphabetic form.

Numeric Date Format

Microsoft® SQL Server™ 2000 allows you to specify date data with a numeric month specified. For example, 5/20/97 represents the twentieth day of May, 1997. When using numeric date format, specify the month, day, and year in a string with slash marks (/), hyphens (-), or periods (.) as separators. This string must appear in the following form:

number separator number separator number [time] [time]

These numeric formats are valid:

[0]4/15/[19]96 -- (mdy)
[0]4-15-[19]96 -- (mdy)
[0]4.15.[19]96 -- (mdy)
[04]/[19]96/15 -- (myd)
15/[0]4/[19]96 -- (dmy)
15/[19]96/[0]4 -- (dym)
[19]96/15/[0]4 -- (ydm)
[19]96/[04]/15 -- (ymd)

When the language is set to us_english, the default order for the date is mdy. You can change the date order with the SET DATEFORMAT statement, which can also affect the date order, depending on the language.

The setting for SET DATEFORMAT determines how date values are interpreted. If the order does not match the setting, the values are not interpreted as dates (because they are out of range), or the values are misinterpreted. For example, 12/10/08 can be interpreted as one of six dates, depending on the DATEFORMAT setting.

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

Let's review a few examples on setting SQL Server date/time variables with not only the date portion, but also the time portion. The following example will insert the same date and time (October 30, 1956 2:01:29.000 PM) into a table, where the time is specified in different SQL Server acceptable formats.

SET NOCOUNT ON

CREATE TABLE X(EXAMPLE INT, D DATETIME)

-- EXAMPLE 1

INSERT INTO X VALUES(1, '19561030 2:01:29 PM')

-- EXAMPLE 2

INSERT INTO X VALUES(2, '19561030 14:01:29')

-- EXAMPLE 3

INSERT INTO X VALUES(3, '19561030 14:1:29 PM')

-- EXAMPLE 4

INSERT INTO X VALUES(4, '19561030 14:01:29.000 PM')

SELECT * FROM X ORDER BY EXAMPLE

DROP TABLE X

Note that you can enter the time with or without the PM identifier, provided the hour value is between 13 and 23. If Example 1 was to not have the PM identifier then SQL Server would have interpreted that time value as 2:01:29 AM. Also, since we are placing these dates into a datetime column within table X, each that does not have a millisecond identifier will default to '000' milliseconds'

Let review another example that does something different with the time when saving a time value in HH:MM:SS format into a smalldate variable. Remember a smalldate variable will only store time down to the minute. Here is the example script:

SET NOCOUNT ON

CREATE TABLE X(EXAMPLE INT, D SMALLDATETIME)

-- EXAMPLE 1

INSERT INTO X VALUES(1, '19561030 2:01:29 PM')

-- EXAMPLE 2

INSERT INTO X VALUES(2, '19561030 14:01:30')

-- EXAMPLE 3

INSERT INTO X VALUES(3, '19561030 14:01:29.999 PM')

SELECT * FROM X

DROP TABLE X

Here is the output from the "SELECT" statement in the above script:

EXAMPLE     D                                                      

----------- 	------------------------------------------------------ 

1           	1956-10-30 14:01:00

2           	1956-10-30 14:02:00

3           	1956-10-30 14:02:00

If you look at the times that were inserted they all had the same minute. So why did SQL Server change the minute on a couple of the values? This was done because SQL Server does some rounding of the seconds and milliseconds when trying to determine the minute that was being inserted.

SQL Server also has a function to validate your input data to verify that it really contains a date. The name of this fuction is ISDATE. This function determines whether an input expression is a valid date, based on whether the input date meets one of SQL Server's designated date formats, as described above.

The ISDATE function returns a 1 when a valid date expression is pass to the function, and a 0 if an invalid date expression is passed. Here is an example on how one might use the ISDATE function to validate input data.

SET NOCOUNT ON

CREATE TABLE X(VALID_DATE DATETIME)

DECLARE @IDATE1 CHAR(20)

SET @IDATE1 = '10-30-1956'

IF ISDATE (@IDATE1) = 1

BEGIN

  PRINT 'VALID DATE: ' + @IDATE1 + ' ROW INSERTED'

  INSERT INTO X VALUES (@IDATE1)

END

ELSE 

  PRINT 'INVALID DATE: ' + @IDATE1



SET @IDATE1 = '10-30-1956 AM'

IF ISDATE (@IDATE1) = 1

  INSERT INTO X VALUES (@IDATE1)

ELSE 

  PRINT 'INVALID DATE: ' + @IDATE1 + ' INSERT NOT PERFORMED'

SELECT * FROM X

DROP TABLE X

Output from this example:

VALID DATE: 10-30-1956           ROW INSERTED

INVALID DATE: 10-30-1956 AM        INSERT NOT PERFORMED

VALID_DATE                                             

------------------------------------------------------ 

1956-10-30 00:00:00.000

From this example, you can see that the first setting of the variable @IDATE1 has a valid date and a row was inserted into table X. Although the second setting of @IDATE1 does not contain a valid date and therefore the ISNULL function returned a 0 and caused an INVALID DATE message to be displayed.

Depending what you are trying to accomplish, the ISDATE function might be a method to validate your input date prior to having SQL Server reject your date when trying to place it into a DATETIME or SMALLDATETIME variable.

Conclusion

As you can see, there are many different formats and things to consider when using character strings to populate date/time columns. Care needs to be taken to make sure the character strings are in the proper format, and whether rounding of milliseconds, or seconds will give you a final time that you are not expecting.

This article only dealt with inserting dates into SQL Server. My next article, part 2, will discuss displaying datetime and smalldate time column values in different formats.

» See All Articles by Columnist Gregory A. Larsen