March 5, 2017 at 9:16 am
I have a date field of type datetime in a table.
But while I'm creating a view out of this field and others, I need to format the date to be 'dd/MM/yyyy'
but need to keep it as a date type.
FORMAT and/or CONVERT changes the output type to varchar.
So how do you format the output but keep it as datetime?
Walter
March 5, 2017 at 11:59 am
A date, by definition, has no format because it's stored internally as numbers. Format is something specific to the string representation of a date.
In short, you can't. If you want a specific format, you get it by converting the date to a string in that specific format
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2017 at 12:00 pm
You can have some impact on the format by changing server level settings insofar as what order the date parts will appear in but, other than that, the answer is "Can't be done".
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2017 at 12:38 pm
Depending on what you're trying to do you could use two columns. One with the date formatted as a string the way you want to display it and another with the date as a datetime for use in a where clause or order by or some other calculation you might what to do that requires the datetime format.
March 6, 2017 at 12:02 pm
walter.dziuba - Sunday, March 5, 2017 9:16 AMI have a date field of type datetime in a table.But while I'm creating a view out of this field and others, I need to format the date to be 'dd/MM/yyyy'
but need to keep it as a date type.FORMAT and/or CONVERT changes the output type to varchar.
So how do you format the output but keep it as datetime?
Walter
Let's begin with the basics. There is a difference between datatypes and the display of the data types. Do you understand the difference between the number two, the Roman Numeral II, the hexadecimal representation, the Egyptian representation, the Chinese representation, etc. Numbers are more abstract the numerals.
In the case of SQL, the only display format allowed in the ANSI/ISO standard is based on ISO 8601; it is "YYYY-MM-DD HH:MM:SS" and anything else is a local dialect.
Even worse than that is you don't know what a field is! Have you ever read the ANSI/ISO standard SQL specs? Probably not, because they are boring as hell. Only a person interested in being a really professional database person or who is anal-retentive would bother with it (remember he helped write them). 🙂 In the case of temporal data a field is a part of a temporal value, which makes no sense by itself {year, month, day, hour, minute, second}.
Finally, in modern data architectures ,we have tiers. The database layer deals with nothing but the data. The presentation layers deal with nothing but the presentation, such as your formatting. They are separate! This is not just RDBMS and SQL, but the basis of all modern programming since about 1975.
Your whole mindset is completely wrong, as well as your terminology. Internally, you have no idea how a timestamp is stored (Microsoft still uses the term datetime and datetime2(n) , but they've always been a little bit behind and are still carrying the old Sybase legacy).
Were you a COBOL programmer? In that language, dates and everything else are stored as strings, so your mindset would make some sense.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply