July 22, 2009 at 4:11 am
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
July 22, 2009 at 5:24 am
'20090722 00:00:00' should work with any date format.
July 22, 2009 at 5:37 am
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.
July 22, 2009 at 5:52 am
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 shifthistoryIt 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)
July 22, 2009 at 6:22 am
And how about
select * from shifthistory where vfrom='20090723 00:00:00'
July 22, 2009 at 6:34 am
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.
July 22, 2009 at 9:05 am
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.
July 22, 2009 at 9:52 am
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.
July 22, 2009 at 11:24 am
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.
July 22, 2009 at 11:37 am
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.
July 22, 2009 at 1:01 pm
U r right Lamprey, the reports are using varchar instead of datetime, Some thing that probably be looked into for future releases.
Lynn, i changed the language to English, by rt clicking the server in object explorer and then restarted the services but it still did not change the language to English. If i do rt click properties it still says Spanish. Am i doing something wrong?
July 22, 2009 at 1:07 pm
No idea. It isn't something I have needed to do and I'm not going to try it here at work even if I do have access to a test server.
The only options you may have is to uninstall and reinstall SQL Server, or modify the queries appropriately. Which do you see being the easier to accomplish?
July 23, 2009 at 2:32 am
As a quick work around i have changed the logins language as english.
For future purposes we need to look at amending the queries. wat is the best way to do this? Will data type datetime be able to tackle this issue where date formats are differerent in different countries.?
July 23, 2009 at 6:50 am
A datetime value is a datetime value. It doesn't care what the regional settings are. You need to concern yourself on that when converting between character and datetime values.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply