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 12»»

Changing the default date format of SQL server 2005 Expand / Collapse
Author
Message
Posted Wednesday, July 22, 2009 4:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:49 AM
Points: 357, Visits: 1,720
How to change the default Date format.

I have default language set up as Spanish. But it uses the US date format. So my Querries are failing.

Is there a way i can change the default date format of the SQL server.

The collation is set to : SQL_Latin1_General_CP1_CI_AS.

The querries fail if the date format is in '2009-07-22 00:00:00' format and works if the date format is in '22-07-2009 00:00:00'

Thanks in advance.

Sarvesh


blog: http://sarveshsingh.com

Twitter: @sarveshsing
Post #757282
Posted Wednesday, July 22, 2009 5:24 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 15, 2014 9:03 AM
Points: 851, Visits: 5,596
'20090722 00:00:00' should work with any date format.
Post #757304
Posted Wednesday, July 22, 2009 5:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:49 AM
Points: 357, Visits: 1,720
When i do rt click properties on SQL server it says language spanish.

When i run a query :

select * from shifthistory where vfrom='2009-07-23 00:00:00'. i get an error.



but when i use

select * from shifthistory where vfrom='23-07-2009 00:00:00' It works.

If i just run
select * from shifthistory  

It give me a result of vfrom as '2009-07-23 00:00:00' but when i use this date in the where clause it errors.



blog: http://sarveshsingh.com

Twitter: @sarveshsing
Post #757310
Posted Wednesday, July 22, 2009 5:52 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 20,857, Visits: 32,877
sarvesh singh (7/22/2009)
When i do rt click properties on SQL server it says language spanish.

When i run a query :

select * from shifthistory where vfrom='2009-07-23 00:00:00'. i get an error.



but when i use

select * from shifthistory where vfrom='23-07-2009 00:00:00' It works.

If i just run
select * from shifthistory  

It give me a result of vfrom as '2009-07-23 00:00:00' but when i use this date in the where clause it errors.



Does this work?

select * from shifthistory where vfrom = convert(datetime, '2009-07-23 00:00:00', 120)





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #757318
Posted Wednesday, July 22, 2009 6:22 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 15, 2014 9:03 AM
Points: 851, Visits: 5,596
And how about

select * from shifthistory where vfrom='20090723 00:00:00'

Post #757348
Posted Wednesday, July 22, 2009 6:34 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 20,857, Visits: 32,877
The reason it is failing is because your default date format is dmy. Here is some test code I ran here at home.

set dateformat mdy
select cast('2009-07-23 00:00:00' as datetime) -- works
go
set dateformat dmy
select cast('2009-07-23 00:00:00' as datetime) -- fails
go
set dateformat dmy
select cast('23-07-2009 00:00:00' as datetime) -- works
go

If you add set dateformat mdy before your first query, it will (or should) work.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #757364
Posted Wednesday, July 22, 2009 9:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:49 AM
Points: 357, Visits: 1,720
Hi Lynn; thanks for your response. But i have got about 100 reports which use the dates.. these reports work for our US customers. It's not working for our Spanish Customers.

The difference is the SQL server 2005 Properties in the language it says Spanish instead of English US.. How can i change that back to English US.

Your Query does not give me error, but that would mean changing 100 reports..

Is there a way i can change the language toi English US.


blog: http://sarveshsingh.com

Twitter: @sarveshsing
Post #757520
Posted Wednesday, July 22, 2009 9:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:49 AM
Points: 357, Visits: 1,720
I've just changed the login to English and that seems to work.

But i'll still be interested to know if the SQL server Language can be changed to US_English from spanish.


blog: http://sarveshsingh.com

Twitter: @sarveshsing
Post #757604
Posted Wednesday, July 22, 2009 11:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 2:12 PM
Points: 292, Visits: 1,625
Are you using VARCHAR instead of DATETIME to pass parameter around? If so, you are bound to have issues in a muti-cultrual deployment. It may not be the easiest solution, but, probably, the best solution is to use proper datatypes.
Post #757706
Posted Wednesday, July 22, 2009 11:37 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 20,857, Visits: 32,877
Looks like you should be able to accomplish this task. Right click on the server in the Object Explorer, go to the Advanced Page. You should be able to change the language there.

Be sure to let us know. Also, You may want to test this on a development or test server first.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #757717
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse