Converting Date with T-SQL

[jconway]

I have a date in ISO 112 format. This is YYYYMMDD. I want to convert it to the normal US mm/dd/yyyy format. I know there's a quick way, but just can't pin it down. Please help. Thanks, Jen

 

[gmmastros]

Declare @Date DateTime
Set @Date = '20080111'

Select Convert(VarChar(10), @Date, [!]101[/!])

The 101 on the end is what converts this to the mm/dd/yyyy format.

However, I would recommend that you store this as a DateTime and return it as a date time. This way, your front end application can format the results based on regional settings (for the computer).

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

If you are starting with a 'string' data type (char, varchar, nchar, nvarchar) then you could just convert twice, like this...

Select Convert(VarChar(10), Convert(DateTime, '20080111'), 101)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jconway]

That's not working. I tried it before posting...

convert(varchar(10),D.DispDateKey,101) as DispDate,

and my results are in the same format - YYYYMMDD

 

[gmmastros]

[tt][blue]
Convert(varchar(10),[!]Convert([/!]DateTime, D.DispDateKey[!])[/!],101) as DispDate,[/blue][/tt]

I strongly encourage you to store this as a proper DateTime data type. One day, the boss is gonna come along as say, can you tell me the average number of days between this and that. Or... how many of these happened in February?

When you store dates in a datetime column, these queries are a lot easier to accomplish.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom