SQL Server Datetime issues. American vs. British?

sql-server sql-server-2005 datetime date-formatting
super9 picture super9 · Jul 27, 2009 · Viewed 20.8k times · Source

On my test DB, the dates are displayed in a DD/MM/YYYY format. By displayed I mean when you right click, open table in Management Studio, the returned data are displayed in a DD/MM/YYYY format.

Funny thing is, when I write T-SQL to retrieve records, I have to input a MM/DD/YYYY format to get back the right data. Is there anyway I can align this to a DD/MM/YYYY format?

Answer

mmx picture mmx · Jul 27, 2009

You can use SET LANGUAGE to choose the date format that SQL Server expects in queries (I think management studio uses client computer's regional settings for display purposes, not sure though). However, I suggest passing values using parameters instead of embedding them in query statement. You won't encounter any issues if you use parameters. Everything is taken care of.

set language us_english
declare @d datetime = '1929/12/18'

set language british
declare @d datetime = '1929/12/18' -- fails

To change the server default language:

declare @langid int = (select langid from syslanguages where name = 'british')
exec sp_configure 'default language', @langid
reconfigure with override

Related questions

SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More
Sql Server string to date conversion

I want to convert a string like this: '10/15/2008 10:06:32 PM' into the equivalent DATETIME value in Sql Server. In Oracle, I would say this: TO_DATE('10/15/2008 10:06:32 PM','MM/DD/YYYY HH:MI:SS AM') This question implies that I …

Read More
How to get Time from DateTime format in SQL?

I want to get only Time from DateTime column using SQL query using SQL Server 2005 and 2008 Default output: AttDate == 2011-02-09 13:09:00 2011-02-09 14:10:00 I'd like this output: AttDate Time == 2011-02-09 13:09:00 13:09 2011-02-09 14:10:00 14:10

Read More