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.
If the goal is to strip the time - and just return a date then use cast instead: CAST(date_received AS date).
The date, datetime and datetime2 data types do not have a format - so 'converting' to mm/dd/yyyy will always be a string representation of the date.
When you are converting TO date, the style tells the function the format of the input data and will error if the input does not match that format. When you convert FROM a date, say to a varchar, the style tells you the output style.
If successful a CONVERT(date,...) returns a date type, which is actually just an integer under the covers. It has no formatting.
OK, I think I'm following both of you (thanks).
Are we basically saying then, that there is no way to do what I really want (this has to be common it seems like?) - which is to make it LOOK like mm/dd/yyyy but have it retain its date datatype.
Where do you want to show the formatted dates? If you use front end application, do the formation there. When you convert to mm/dd/yyyy it is a varchar and not a date
I think the key is "What are you displaying the data in"?
Sometime I format the data coming out of SQL because the thing that is receiving it can't - e.g. a Text file - but for Excel or any application I export it as a native date format and make the application do the formatting (the Application then has the dates in Native Date Format so can sort/manipulate them accordingly)
OK, so basically there isn't a way to make it look any particular way and still retain its date datatype.
I know what you mean - the only reason I was doing it this way is because I am creating a view for end-users to do occasionally ad-hoc querying on using ODBC in, for example, Excel or MS Access.
While WE know that a date is a date is a date and the final app/display/report is the thing that should handle the display worries, end users don't see it like that and I was just thinking pre-emptively of them asking if it could be formatted differently, but then again I also know they expect a date and they expect to be able to query it as a date, so it must be left a date. (in other words it needs to be a date because they'll be using it for ad-hoc roughly hewn queries, but....I was just proactively thinking they might expect me to be able to make it "look" a certain way at the same time - that's not the case so I'll just leave it a date and move on).
Thanks.
When we pull data into Excel (for example) we pull it in a format that Excel treats as a date (I've forgotten exactly, I suspect it is a Text Date, not a Date Datatype, but Excell is guaranteed to say "This column contains dates").
Then we apply a Format, in Excel, for that column so that the dates present in the way that the user likes them. Once Excel has decided the column contains dates you can format it how you like, in Excel, but the underlying data is still dates (as far as Excel is concerned).
Things will be easier in Access as that has a Date datatype, so you can make a table/query/whatever that has a date datatype column ... if you then pull data from SQL into that Access table it will, natively, preserve the dates. Then when the user queries them they will still be dates - the user (or you!) can set them to a format that suits the user.
So basically, as we were saying above, the data remains as a "Date type" all the way to the application, and then the application does the formatting. The Application still knows its a date, so the user can sort, calculate DIFF-days/hours etc., select BETWEEN two dates, and all that good stuff.
Yes, I understand all that - I've worked as an Access/Excel developer for years before starting SQL. My question was really just whether it was possible to make it look like a certain style while retaining the SQL date/datetime datatpye. Apparently the answer is no - fair enough. Thanks.
You cannot rely on the default format of EXCEL. Many times the string like 10-30 is displayed as date whereas actual dates are sometimes displayed as integers 
1 Like