Setting the DATEFORMAT for each connection can be cumbersome if all your data comes from the same source in the same format. To streamline the process, you can change your default language. To define a new language, you need only the names for the days of the week, the names of the months, and a default date format.
The stored procedure sp_ helplanguage contains several language definitions with only these elements. If you want to use the English names of months and days, but use a default input format of DMY instead of MDY, you can change the language used by any login to the British format. You can use sp_defaultlanguage to make this change:
sp_defaultlanguage sue, british
Now, whenever user Sue logs in, SQL Server will interpret dates in her requests as DMY (day-month-year). If she executes the command
SELECT convert(datetime, '3/4/48')
she will get the result
2048-04-03 00:00:00.000
Special Formats
Your default language or DATEFORMAT setting never affects the ISO standard format. SQL Server will always interpret the ISO standard format, with all numbers and no punctuation, as YMD. So regardless of whether Sue (with her British default format) or I (with my US format) enter the command
select convert(datetime, '20001012')
we'll both get a date that specifies October 12, 2000. Make sure that you always put dates in single quotes. SQL Server applies your DATEFORMAT setting only when it converts character strings to datetime values. If you omit the quotes in the example above, SQL Server will assume that you meant the number 20,001,012 and will interpret that figure as the number of days after the base date of January 1, 1900. The result would be outside the range of possible datetime values that SQL Server can store.
One other date format can override your default language or DATEFORMAT setting, but this format behaves somewhat inconsistently. If you enter a date in all numeric format with the year first but you include punctuation (as in 1999.05.02), SQL Server will assume that the first part is the year, even if your DATEFORMAT is DMY. How does SQL Server determine which number is the month and which is the day? SQL Server will still use your DATEFORMAT setting to determine the order of the month and the date values. So, if your DATEFORMAT is MDY, the following statement
select convert(datetime, '1999.5.2')
will return
1999-05-02 00:00:00.000
If your DATEFORMAT is DMY, then
convert(datetime, '1999.5.2')
will return
1999-02-05 00:00:00.000
and
convert(datetime, '1999.5.22')
will return an out-of-range error. This behavior seems quite inconsistent to me. SQL Server partially ignores the DATEFORMAT, but not completely. I suggest that you avoid this format for inputting dates, and omit the punctuation if you use all numbers.
Next month, I'll continue to look at the input format. Also, I'll tell you how SQL Server determines which century you mean when you enter a two-digit year, why you can't have a date before 1753, and why SQL Server seems to produce strange rounding errors when converting data from datetime to smalldatetime types. I'll also show you some coding tricks for searching for various datetime values in your tables. So, do we have a date?