Working with SQL Server Date/Time Variables - Page 3
April 16, 2003
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.
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.
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.