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
» 
Comment and Contribute
