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