SQL Server 2008, how to set date format

[rkbnair]

In SQL Server 2008 Management Studio, how can we set the date display format to mm/dd/yyyy? Is to be done every session? Or, is it something that to be set at the database level?

Thanks.

[Hack]

Does this help?

[rkbnair]

No, it doesn't help. There is no need to all these formatting. When you open the contents of the table, the date fields should default to a format.

[Emefa]

rkbnair

SQL Server is different from Microsoft Access.
It does not have the fancy look of showing date out of the box in a format like Access does.
When you run a query, you then determine at that point what format you want the date to be.
Other things you could do is to do the following:

Set the database collation to Latin1_General_CI_AS and probably set the language to US English.
http://msdn.microsoft.com/en-us/library/ms187928.aspx

The link provided above will help you format date when running a query.

If you open a query window, you can run this:

SELECT convert(char(10),getdate(),101)

That should return the date in mm/dd/yyyy for you.
If you wish to run the query against tableA with date column being MyDate,
you will use

SELECT 
          convert(char(10),MyDate,101) 
FROM
          tableA

Does this help?
Emefa

[rkbnair]

Agree. But it shouldn't be much different from SQL 2005, right ?

Why it is defaulted in SQL 2005 and not available in 2008?

[Emefa]

Good question.
Sure enough, I fired up my 2008 and 2005 side by side and you are very right.
I wonder why they changed it.
I know the concept of Date and Datetime2(7) got added.
That probably explains why the difference is the way it is.
Don't you just love it when things like this change from version to version?
I bet someone will write an article about this if it is not already out there somewhere.

Emefa

[rkbnair]

yeah, you are absolutely right. Should have written an article

[Hack]

This is as close to an article on the subject as I could find.

[unenforcibility]

I don't have sql server 2008 yet but thanks, I'll keep in mind

[pinswitiz]

use AQL 2008 that's good

[Hack]

What is AQL?