British and american date formats

  • justy

    Old Hand

    Points: 311

    Hi,

    I have one server that defaults to a British date format (DD/MM/YYYY) when a connection is made, and another server defaults to an American data format (MM/DD/YYYY) when a connection is made.

    Is there a database property I can set anywhere that specifies the date format for new connections, or is this managed elsewhere?

  • rsharma

    SSCarpal Tunnel

    Points: 4865

    You can either:

    a) Specify a default language for the login (using sp_defaultlanguage), or

    b) use the SET DATEFORMAT accordingly for the session.

  • justy

    Old Hand

    Points: 311

    The default language on the second server was set to English not British English. I guess British English forces the dateformat to a british date format.

    Thanks!

  • David A. Long

    SSCrazy Eights

    Points: 8421

    Actually all SQL datetime values are the same, it is the language setting or SET DATEFORMAT that changes the way it translates a datetime to/from text.

    For comparison or assignment, always use either the ISO or ODBC canonical formats, and your code will not care which language setting or SET DATEFORMAT has been assigned.

    Example: ISO = '20051201' ODBC canonical = '2005-12-01 00:53:23.644'

    There are ODBC or OLE DB connection string settings that will translate the date and numeric formats to that of the user's Windows Regional settings.

    Andy

  • jalvarez-797918

    SSC Enthusiast

    Points: 151

    I used the ISO format and didn't work.

    I'm using a Smalldatetime field and tried to do the assign :

    VigenciaSeguro = '20030721

    Instead of a succes message I get a Msg 296, Level 16 error message...

    "La conversión del tipo de datos char a smalldatetime generó un valor smalldatetime fuera de intervalo."

    "Data type conversion char to smalldatetime generates an out of range interval smalldatetime value"....

  • Andy sql

    SSCrazy Eights

    Points: 9369

    The ISO date format should work. Are you sure the date you are trying to assign is valid?

  • jalvarez-797918

    SSC Enthusiast

    Points: 151

    Hi. Thanks for answer so fast.

    VigenciaSeguro is an smalldate field in my SQL Server table.

    I'm trying to assign the value : '20030721' , wich is a date in ISO format, but SQL don´t recognize it as a valid date.

    What's the recomended type to store ISO dates an date-hour data?

  • Andy sql

    SSCrazy Eights

    Points: 9369

    It sounds like the date is not what you think it is! Try:

    declare @mySmallDate smalldatetime

    set @mySmallDate = '20030721'

    select @mySmallDate

    That should work just fine. If you try a date which is outside of the range for smalldatetime (roughly the year 1900 to 2079), such as:

    declare @mySmallDate smalldatetime

    set @mySmallDate = '20990721'

    select @mySmallDate

    you replicate the error you are getting. Double-check the date which is being used for the insert (try using Profiler to see exactly what is going on).

    If you want to store dates with a bigger range, use the standard "datetime" datatype.

  • jalvarez-797918

    SSC Enthusiast

    Points: 151

    You where right, It works, the problem was in another assignment in the same UPDATE sentence.

    I'm very surprise. All my live I've strugled with date format in my programs and portability issues for different database vendors and this seems to work in DB2 also.

    One last question. In C# or VB The string format would be: 'yyyyMMdd hh:mm' ?

    Thanks

  • Andy sql

    SSCrazy Eights

    Points: 9369

    There is some debate about the "best" way to represent the date. With MS SQL, probably best to go with ISO 8601 format:

    yyyy-mm-ddThh:mm:ss

    where "T" is the separator between the date and time.

    eg. 2008-07-14T17:06:55

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply