Default language and dates

I've just moved servers - WK3 to WK3 - installed SQLserver 2005 and uploaded the database. Code base has not changed but now I'm getting

"String was not recognized as a valid DateTime.Couldn't store <21/07/2006>" . I suspect its to do with the default language , but although the default is English (United States) as in my last server, I have set the language in Advanced settings to be British English. I can't see any difference between the settings and my last server. I'm British BTW .

I also ran

EXEC @.ret=sp_defaultlanguage'sa','British English'

as the only login is sa . WK3 is itself set to English (United Kingdom) - I thought SQL 2005 would inherit this setting? Any help would be much appreciated.

ashaig:

"String was not recognized as a valid DateTime.Couldn't store <21/07/2006>" . I suspect its to do with the default language , but although the default is English (United States) as in my last server, I have set the language in Advanced settings to be British English. I can't see any difference between the settings and my last server. I'm British BTW .

Yes it may be related to the language setting. Actually there are some differences between English (United States) and British English, including date format. You can use the following command to check details of all language settings:

EXEC sp_helplanguage

From the result we can see the dateformat of us_en is mdy, while the dateformate of British is dmy.

ashaig:

as the only login is sa . WK3 is itself set to English (United Kingdom) - I thought SQL 2005 would inherit this setting? Any help would be much appreciated.

You can change the default language setting of a SQL instance by using such command:

EXEC sp_configure 'default language',0
reconfigure with override

Where 0 is the id of the language. Here are some useful links about the language setting in SQL Server:

Default Language option:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3xny.asp

sp_configure:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_0put.asp

sql

Default dates to UTC in SQL Server 2000

Hi all,

I hope this is the correct forum. I have SQL Server 2000 installed on a server located in the USA. The local time zone on the server is EST. Therefore getdate() returns EST time. I now realise that I'd like to be saving all my timestamps in UTC. While I could modify all my stored procedures to use getutcdate() rather than getdate() I wonder if there is a database level setting for time zone that will cause getdate() to return UTC date?

Any ideas?

Des

Unfortunately, there is not a database or server lever 'switch' that would allow us to shift time zones.

However, it would be fairly quick to search syscomments for getdate() to determine a list of procedures needing alteration.

You would also want to change any table definitions where column level defaults are set to getdate().

Or, you could set the OS Server time for GMT timezone and turn automatic DST off -but that would effect all other non database logging on the server. Perhaps not a good idea...

|||

Thanks Arnie. I thought as much but it's always good to double check.

Regards,

Des

Default Date

Hi,

I have two parameters as StartDate and End Date. these dates are picked from the Datepicker.

What I want to do is to default the start date to the the 1st January of the current year and end date as 31st december of the current year.

I tried to use the default in 'Report Parameters'. But could not get through. Can anyone suggest me the solution.

regards

Josh

Under default values of the report parameters are, you can use expressions to create your default dates (using non-queried option)

EG, 1 Jan in current year, expression would be

=DateSerial(Year(Today),1,1)

and 31 Dec

=DateSerial(Year(Today),12,31)

|||

Hi Will,

Thanks a lot! This worked and was very helpful.

Regards

Josh

Debugging Query Analyzer & dates

Two of the sprocs input parameters are datetime. When I try to debug the
sproc SQL Server errors with
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
I've tried everything (except of course what might actually work).
Help!!!
regards
Frank Ashley
Try any of the ODBC styles, as defined in:
http://msdn.microsoft.com/library/en...on_03_04l0.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:uG9xu73gEHA.632@.TK2MSFTNGP12.phx.gbl...
> Two of the sprocs input parameters are datetime. When I try to debug the
> sproc SQL Server errors with
>
> [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
> specification
>
> I've tried everything (except of course what might actually work).
>
> Help!!!
> regards
> Frank Ashley
>
|||That worked first time.
Thanks
Frank Ashley
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OZ0mPj4gEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Try any of the ODBC styles, as defined in:
> http://msdn.microsoft.com/library/en...on_03_04l0.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
> news:uG9xu73gEHA.632@.TK2MSFTNGP12.phx.gbl...
>