British and american date formats

Question

Post reply

British and american date formats

justy

Old Hand

Points: 371
More actions
November 30, 2005 at 5:51 am

#166367

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?

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

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

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 1

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: 371 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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: 9496 More actions · Answer 5

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

jalvarez-797918 SSC Enthusiast Points: 151 More actions · Answer 6

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: 9496 More actions · Answer 7

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 More actions · Answer 8

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: 9496 More actions · Answer 9

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