dcsimg

Working with SQL Server Date/Time Variables

April 16, 2003

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.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers