Date format doesn't work as expected

[jfcox]

I have a column that I need to get into mm/dd/yyyy format I can get it that way as a varchar but converting to a date gives me a problem. I thought it was my data but look at this example,

select Convert(varchar,getdate(), 101) as date1,
CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 101)) as date2
from test_table

date1 returns 12/20/2005
date2 returns 2005-12-20 00:00:00.000

I've tried lots of variations; every one I can think of without success.

How is this possible? How do I get the right format?

Thanks in advance for anything anyone can add.

 

[SQLBill]

Try:

SELECT (CONVERT(VARCHAR(10),GETDATE(), 101) + ' ' + CONVERT(VARCHAR(10),GETDATE(), 108)) AS Date1

-SQLBill


Posting advice: FAQ481-4875

 

[jfcox]

Thanks, but I need date1 to be a real date otherwise it doesn't sort correctly. I also only want it as mm/dd/yyyy.

How do I convert what you wrote to a datetime?

 

[mrdenny]

When you display a datetime field it will always display in the servers datetime format (unless you change the server or session datetime format). If you want to return it in a specific way you'll need to convert it to varchar as SQLBill has shown and you have been doing.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[gmmastros]

The varchar(8) was converting to: 12/20/20 and then being interpreted as the year 2020.

You should be aware that you cannot change how SQL Server stores it's date. You can, however, change the way you display dates. The convert function one way to do it. Personally, I prefer to return dates from the database in a datetime format and then format the data on the front end.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

You might want to take a look at FAQ183-5834 and FAQ183-5842. Both concern working with dates and times. 5834 is by me and describes how DATETIME works.

-SQLBill

Posting advice: FAQ481-4875

 

[maswien]

What you care is the display format of a date column is mm/dd/yyyy, so date1 is what you want, if sorting is what you concerned then you can do it in following way:

select Convert(varchar, dateCol, 101) as date1
from test_table
order by dateCol