This script came out of a discussion here at the office around a product that isn't flexible or location aware in how it writes datetime values into the database.

This is also not paying attention to storing the date data in UTC or localtime or any of those concerns, just the semantics of storing whatever the date value happens to be.

What are your experiences with this?  Comments as always most welcome

Example script follows:

/*
    You can use the following proc at the start of your app to see
    what the current date format SQL Server is expecting from you
    based on the default language selection set on your login.

    Look at the dateformat field in the resultset of this proc.

    Use this if you want to honour the regional selection that has
    been setup on a per-user basis on the SQL Server
    (login properties of each user)
*/
exec sp_helplanguage @@language

/*
    Otherwise, you may also override the language settings if your
    application code can only format dates one way.

    This will avoid message 241 at runtime:    
        "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

    The 15th of Feb is a good example because there is no 15th month:
*/

SET LANGUAGE us_english
go -- dateformat is now = mdy

select cast('2/15/2003' as datetime) as US_1
go
select cast('15/2/2003' as datetime) as US_2 --Expect Msg 242
go


SET LANGUAGE british
go -- dateformat is now = dmy

select cast('2/15/2003' as datetime) as British_1 --Expect Msg 242
go
select cast('15/2/2003' as datetime) as British_2
go

You can grab the script here: 20070412.deepdark.net_dmy_mdy.sql (1.14 KB)