Changing dateformat on a SQL Server DB and not on whole server to dd/mm/yyyy

Question

Hi there,

can anyone help, we have a sql server 2005 database installed (actually its an sql server 2000 attached to an sql server 2005 server) with the default server language language USA with dateformat like mm/dd/yy and we really need to keep it this way but currently just uploaded a new database to the server and this needs to have the dateformat for dd/mm/yyyy.

Can i force a change just on a database and not the whole server? If i force the change on the whole server it will make all my other apps fail

For example currently we have this sql statement which fails..

SELECT * FROM sesiones WHERE ultimo_acceso < '16/04/2009 13:36:17'

but of course we can add this which now works

SELECT * FROM sesiones WHERE ultimo_acceso < Convert(datetime, '16/04/2009 13:36:17', 103)

but the problem being is that there are a large number of sql statements within the application. The truth being is that the application is fairly old and we don't really want to make any changes to the source code..

SO hence if we could force a a change just on the Database / the tables of specific database then this would suffice

Any help really appreciated

Answer 1

SET dateformat dmy;
SELECT * FROM sesiones WHERE ultimo_acceso < '16/04/2009 13:36:17';

Answer 2

You can change the default date format per user by selecting the default language for that user in SQL Management Studio > Security > Logins > {user properties} > Default language.

And you can see the date format for each language with

EXEC sp_helplanguage

Answer 3

I think you're confused. Datetimes in sql server are not stored as strings at all. They are stored as 8 byte binary values, and only converted to strings when you show them to the user. You are using a Datetime column to hold your dates, right?

Answer 4

When dealing with datetime strings in SQL Server, I would always use the ISO-8601 date format

YYYY-MM-DDTHH:MM:SS

which should work no matter what language or regional settings you have.

Try your select as:

SELECT * FROM sesiones WHERE ultimo_acceso < '2009-04-16T13:36:17'

Marc