I'm confused because a few months ago, a trusted colleague told me that even when I used the convert() function on dates in order to format them as mm/dd/yyyy, they still retained their essential 'data' datatypes, (and could be ordered by, performed date math on, etc. etc).
I really have 2 questions and all of the MSDN pages in the world haven't helped me or clarified sufficiently for me.
- Why does this err on a datetime type field: convert(date,[DATE_RECEIVED],101) as 'DATE_RECEIVED', all it outputs is the typical date looking YYYY-MM-DD- etc. Why is that, if I told the Convert() function to convert it to a DATE, but using style 101 ? Shouldn't it do just that - convert it to a Date (aka, leave it a date, since it already is - which is fine), but convert to style 101?
- So when I go for the other alternative, which is: convert(varchar(10),[DATE_RECEIVED],101) as 'DATE_RECEIVED', I get the desired output "look"......mm/dd/yyyy.......But, it loses the Date nature of its datatype, which I'd rather it didn't, because what use is that to anyone?
Do you see the conundrum - maybe I am missing something here, (obviously), hopefully someone can set me straight. I personally find the MS pages on CAST/CONVERT very, very confusing.