Working with SQL Server Date/Time Variables

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.

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles