SQL Server date format

m1nder

Hi. I'm using SQL Server 2008R2.
I am trying to format the dat eon a database to be in the UK (d/m/y) format. I have tried

set dateformat "DMY" and set dateformat DMY

When I interrogate using dbcc useroptions, I see that the database has
Language : English and DateFormat : DMY.

But yet, when I run a query on a table with a date column, it still returns American date values such as 1999-04-15. Can anyone tell me how to deal with this?

daymobrew

Can you format the date when you go to display it?

The 1999-04-15 is not an American format (they use MDY, not YMD). It is a format that is easy to sort.

Seth Brundle

YYYY-MM-DD is the best date format to work with between DBs and front ends (its the ISO standard IIRC).

I don't think there is a setting to change the date format (but you can use the set dateformat command to override the default).

Anyhow, I tend to use functions to format dates in reports. See http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

m1nder

daymobrew wrote: »

Can you format the date when you go to display it?

The 1999-04-15 is not an American format (they use MDY, not YMD). It is a format that is easy to sort.

Yeah ok but the issue remains.

What I want to do is see the date in UK format when I select on the table. I also want to be able to compare dates using the UK format such as
SELECT *
FROM
Table
WHERE
DateCol > '14/08/2010'

Surely SQL Server will let me do this ? How?

Seth Brundle

SELECT [Field1], [Field2], convert(varchar, [DateColumn], 105) AS MyDate, [Field4]
FROM
Table
WHERE
DateColumn> '2010-08-14'

Bear in mind when working with dates that they also include times (and if none set then they are 00:00)

m1nder

kbannon wrote: »

I don't think there is a setting to change the date format (but you can use the set dateformat command to override the default).

As I mentioned in my op, this does not seem to do anything. What am I missing?

Seth Brundle

Use the code that I gave to format your data. MS SQL isn't the same as Access and isn't really there to make your data look fancy.


Anyhow, see the following for info on the DATEFORMAT function
http://msdn.microsoft.com/en-us/library/ms189491.aspx
http://support.microsoft.com/kb/173907
http://www.aliaspooryorik.com/blog/index.cfm/e/posts.details/post/using-set-dateformat-with-ms-sql-server-290
http://blog.sqlauthority.com/2007/09/28/sql-server-introduction-and-example-for-dateformat-command/

amen

Its been a while but it all depends on the language installed.
You need to lookup sp_addlanguage

This should sort you out.

ClareVisitor

This should work:

SELECT *
FROM
Table
WHERE
CONVERT(varchar(20),DateCol ,103) > '14/08/2010'

m1nder

Well to all who replied...
Console.WriteLine("Many Thanks.");

It seems that you can't easily change the hard date format. All you can do is change the format for inputs such as interrogation.

i.e
set dateformat DMY
will allow me to write
select * from crap where datecol > '05/11/2011'
but it will not change the underlying date format as stored

I'm well aware of CONVERT etc, that's not what I was asking or eluding to.

Many thanks again

FSL

The date time data type is stored in eight bytes the small date time in four bytes. The way the date is displayed depends on whether the server was configured to use regional settings or not.

The way the underlying data is stored is fixed and not in character form.

amen

[PHP]select * from crap where datecol > '05/11/2011' [/PHP]
if you are doing a lot of the above you may get better performance if you do this

[PHP]
DECLARE @MyDateTime DATETIME
SELECT @MyDateTime = CONVERT(DATETIME,'05/11/2011')
select * from crap where datecol > @MyDateTime
[/PHP]

Jagera

m1nder wrote: »

but it will not change the underlying date format as stored

Like FSL said, dates are not stored in any format at all. It is stored in the database as a combination of number of days since 1-jan-1900, and number of milliseconds past midnight.

SET DATEFORMAT doesn't do anything about the DISPLAY of dates - both statements below will output the same format:

set dateformat dmy
select GETDATE()
set dateformat ymd
select GETDATE()

It's to do with the interpretation of date strings:

-- works fine
set dateformat dmy
select CONVERT(datetime, '31/01/2011')
go
-- uh oh..
set dateformat mdy
select CONVERT(datetime, '31/01/2011')

What client tool are you using, Management Studio?