sql date format

patd1
patd1 used Ask the Experts™
on
I have dates stored in my table as 1/1/2010, and I want to read it as 01/01/2010 (MM/DD/YYY)
How can I do that. I tried the following, but still get 1/1/2010.

SELECT CONVERT(VARCHAR(10), [My Column], 101) AS [MM/DD/YYYY] from Temp_crosswalk
result:
7/17/1930
7/17/1930
7/17/1930
1/12/1943

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
I do not see any issues

SELECT CONVERT(VARCHAR(10), getdate(), 101)

returns

08/04/2011
Top Expert 2010
Commented:
What data type are you using for your column?  If you are using [n][var]char, then that is the expected result, and to achieve your aim you will need to first convert to a datetime:

SELECT CONVERT(VARCHAR(10), CONVERT(datetime, [My Column]), 101) AS [MM/DD/YYYY] from Temp_crosswalk

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial