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.
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:
Here is the output from the "SELECT" statement in the above script:
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.
Output from this example:
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.
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.