Convert function note working

I have a field named 'Modified' with a type of 'datetime'. It has entries that look like this:

2014-10-28 12:08:19.067

I want to convert these entries to this:

10/28/2014

But no matter what format I apply I get the same entry like this:

2014-10-28

For instance, when I try this: 

Select CONVERT(Date, Modified , 101) as mod_test from NOTE

I should get 10/28/2014

But I don't. I can change the '101' to any number but I still get the same output. What am I missing? Thanks!


June 19th, 2015 5:22pm

try this

declare @modified datetime = '2014-10-28 12:08:19.067'
Select CONVERT(varchar,CAST(@modified as date) , 101)

  • Marked as answer by Rhino_dance 9 hours 19 minutes ago
Free Windows Admin Tool Kit Click here and download it now
June 19th, 2015 5:44pm

Indeed that works. Thanks for your help!

I've never been strong on what's happening under the hood with these functions, but the methodology seems strange.

Why should I need CAST to turn a DATE field into a... DATE field? And then make it a VARCHAR instead of DATE to render it in a different format? Thanks.

 

June 19th, 2015 5:57pm

No problem.

The CAST converts it from DATETIME (which includes the time portion) to DATE (no time portion), the CONVERT uses a varchar so it displays correctly (based on your chosen format) 

The display format is generally handled by the presentation layer rather than a conversion by the database engine. Best practice is to leave the storage to SQL Server and presentation to the calling application :)

Free Windows Admin Tool Kit Click here and download it now
June 19th, 2015 6:17pm

declare @modified datetime = '2014-10-28 12:08:19.067'
Select CONVERT(varchar(10),@modified , 101)
June 19th, 2015 7:38pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics