Date Time Mayhem in SQL2000
I asked this in the SQL forum with no joy, so apologies if you have read it before, however i thoguht i would try this forum as the problem does involve asp output. Cheers
We are migrating from our current server:
Database: SQL Server 7
OS: Windows NT
To a new server
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 dates as parameters in my asp website 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 to the webpage 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.