Take the 2-minute tour ×

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

share|improve this question
+1. Dates are always a pain. Personally I think the world should all just use YYYYMMDD. –  Dead account Apr 16 '09 at 12:25
@Ian except that 19700102 is ambiguous ;-P –  Ed Guiness Apr 30 '09 at 15:35
add comment

4 Answers

up vote 4 down vote accepted

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
share|improve this answer
Perfect, yes .. changed the users default language –  mark smith Apr 30 '09 at 10:40
+1 awesome tips for an sql neophyte ;-) –  Eddie B Apr 11 '13 at 4:39
add comment
SET dateformat dmy;
share|improve this answer
now i really wonder if this could be what mark asked for :) if this gets accepted, you'll have my vote... –  Peter Perháč Apr 16 '09 at 12:14
add comment

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?

share|improve this answer
add comment

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


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'


share|improve this answer
add comment

Your Answer


