Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


British and american date formats


British and american date formats

Author
Message
justy
justy
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 6
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
rsharma
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 185

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
justy
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 6

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
David A. Long
SSChasing Mays
SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)

Group: General Forum Members
Points: 643 Visits: 237

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
jalvarez-797918
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 11
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
Andy sql
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1150 Visits: 1301
The ISO date format should work. Are you sure the date you are trying to assign is valid?
jalvarez-797918
jalvarez-797918
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 11
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
Andy sql
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1150 Visits: 1301
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
jalvarez-797918
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 11
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
Andy sql
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1150 Visits: 1301
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search