Here Goes....

We are migrating from our current server:
Details
located: UK
Database: SQL Server 7
OS: Windows NT

To a new server
Details
located: US
Database: SQL Server 2000
OS: Windows 2003

To move the databases i did a back up and restore. All dates within datetime fields in old databases showed dates in YYYY-MM-DD HH:MM:SS

When i did the initial restore to the new database, ontop of a blank one which i set up, the dates came through in MM-DD-YYYY. So i went into the server regional settings and made them UK and this seemed to resolve theproblem by changing the existing dates to DD-MM-YYYY and also creating any new ones added in the same fashion (including restires of other databases) ....still with me

This meant that my queries which took sates as parameters in my app and queried the database based on these parametres now worked. However i notice that even though the dates in the database tables are in UK format and the short date format in the regional settings is in dd/mm/yy (i changed the short date format to be this, instead of yyyy) when i do a query and return dates from the database the format i get back is MM/DD/YYYY e.g. 5/25/2005. I dont specify a locale in the code but never did with the old server and the System DSN i am using have th elanguage set to British English and are set to output using the regional settings.

This is driving me crazy any help would be most appreciated.

first of all: the dates are stored in a special binary format that is independent of anything (the client locale, server locale, etc.).

what app are you using to query and to get this '5/25/2005'?

defiant

cheers defiant

sorry should have explained its an asp page that just queries the database in a sql statement. As i say the code is exactly the same as what was on the old server but the dates output onto the page differently

the asp page runs on a computer inside iis, under a certain windows user (system, or Administrator, or something like this i think). maybe this users regional settings are to display the short date in the unwanted format.

i'm not an asp developer, but maybe you can format the dates like this: format$(datevar, "dd/mm/yy"), and if the datevar's data tape is date, (or type variant, subtype date), then it doesn't matter what settings the windows user has, or the sql server user, or whatever, you will get the wanted result.

defiant.

Thanks again for your help Defiant...

I have been back into the Regional and Language Options in the settings on the server and under the advanced settings i change the language for non-unicode settings to be british english instead of US english and this seems to have done the trick.

For the moment...

You may like to inform me from where you change the date format.
where is Regional and Language Options setting ?

I am using SQL SERVER 2000 Enterprise Manager.

Thanks for your guide.

Mateen



ote]Originally posted by dfalconer
 Thanks again for your help Defiant...

I have been back into the Regional and Language Options in the settings on the server and under the advanced settings i change the language for non-unicode settings to be british english instead of US english and this seems to have done the trick.

For the moment...


[/quote]

Apologies Mateen i have not been on the forum for a bit.

I set the Regional and Language Options on the server itself, under the setting > control panel > Regional and Language options

I also ensure that my DSN has the use "regional settings when outputing date" box ticked.

In SQL Server itself under server settings i ensure that the default language in my case is "British English"

Thanks


[/quote]

thanks for response.

there is one confusion,
Regional and Language setting under the control panel
it is user computer language setting ?
to change on server it language it will also change the SQL Server 2000 database language setting ?

I want to change sql server 2000 database language setting.

Regards

Mateen




[/quote]

The language for the SQL Server is set when SQL is installed and cannot be changed.

If you set the regional options for the server to be the date format you want when you view tables in SQL server you will see dates in that format.

The output onto a users machine is controlled by the language setting in the DSN (i do not know if the users regioanl settings overide this).

I basically got to the situation i wanted by trial and error with thre various options

I have a similar problem,

When running date/time within an SQL database (connecting with asp.net) it displays the incorrect date format (US) instead of (UK)

I know the machine is fully set to UK, although the machine is in the US

Regional/language/dates/times/keyboard/ all set to UK (british)

So it got me thinking, i remember using ASP to connect to SQL and it displays the date correctly

to clarify

connecting to SQL db with ASP.net displays incorrect DATE FORMAT
connecting to sql db with asp displays correct DATE FORMAT

e.g

http://www.clickhosting.co.uk/time.asp ! - CORRECT
http://www.clickhosting.co.uk/time.aspx ! - INCORRECT

Is there anywhere inside ASP.net where the date/time format needs changing ?

any help would be greatly appreciated