Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

British and american date formats Expand / Collapse
Author
Message
Posted Wednesday, November 30, 2005 5:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 25, 2007 9:11 AM
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?



Post #240683
Posted Wednesday, November 30, 2005 7:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 9:26 AM
Points: 265, 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.

Post #240736
Posted Wednesday, November 30, 2005 7:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 25, 2007 9:11 AM
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!




Post #240745
Posted Thursday, December 1, 2005 2:14 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, July 3, 2014 2:36 PM
Points: 631, Visits: 236

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




Post #241030
Posted Sunday, July 13, 2008 11:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 7, 2008 8:40 AM
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"....
Post #533165
Posted Monday, July 14, 2008 3:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:39 AM
Points: 1,026, Visits: 1,097
The ISO date format should work. Are you sure the date you are trying to assign is valid?
Post #533350
Posted Monday, July 14, 2008 8:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 7, 2008 8:40 AM
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?

Post #533531
Posted Monday, July 14, 2008 8:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:39 AM
Points: 1,026, Visits: 1,097
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.
Post #533549
Posted Monday, July 14, 2008 8:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 7, 2008 8:40 AM
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
Post #533592
Posted Monday, July 14, 2008 9:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:39 AM
Points: 1,026, Visits: 1,097
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
Post #533614
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse