Working with SQL Server Date/Time Variables - Page 4

April 16, 2003

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