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


Changing the default date format of SQL server 2005


Changing the default date format of SQL server 2005

Author
Message
ss-457805
ss-457805
Old Hand
Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)

Group: General Forum Members
Points: 379 Visits: 1878
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
Ken McKelvey
Ken McKelvey
SSC Eights!
SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)

Group: General Forum Members
Points: 958 Visits: 7267
'20090722 00:00:00' should work with any date format.
ss-457805
ss-457805
Old Hand
Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)

Group: General Forum Members
Points: 379 Visits: 1878
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24233 Visits: 37978
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)



Cool
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)
Ken McKelvey
Ken McKelvey
SSC Eights!
SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)

Group: General Forum Members
Points: 958 Visits: 7267
And how about

select * from shifthistory where vfrom='20090723 00:00:00'
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24233 Visits: 37978
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.

Cool
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)
ss-457805
ss-457805
Old Hand
Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)

Group: General Forum Members
Points: 379 Visits: 1878
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
ss-457805
ss-457805
Old Hand
Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)

Group: General Forum Members
Points: 379 Visits: 1878
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
Lamprey13
Lamprey13
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 1657
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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24233 Visits: 37978
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.

Cool
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)
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