Convert DATETIME to a date in ISO format
-
I have a field, DATO, which is formatted as DATETIME.
There is no time part anyway.
If I use:
SELECT CONVERT(DATE,MAX(dato),112) FROM dbo.dutyrostershift WHERE employeeid = 11
I get
2019-12-29
Instead of
29-12-2019
I have looked in 'Search' in this forum without getting anything useable - So now you get the question
Best regards
Edvard Korsbæk
-
Instead of 112 you need 105
SELECT CONVERT(DATE,MAX(dato),105) FROM dbo.dutyrostershift WHERE employeeid = 11
- Jonathan AC Roberts wrote:
Instead of 112 you need 105
You need to change the data type too (to
varchar), as thedatedatatype doesn't have a format attached to it; it's stored as a binary value and then the application gives it a readable display format. Using a Style code when converting to a date(time) data type tells the data engine what format the literal string is, not what display format the date should be. You need to convert to avarcharto enforce a "format". Note that as soon as you convert the value to avarcharit is no longer a date(time), so applications will not treat it as one; which could lead to unexpected behaviour (like'29-04-2019'being after'01-05-2019') :SELECT CONVERT(varchar(10),MAX(dato),105)
FROM dbo.dutyrostershift
WHERE employeeid = 11;Also the format
dd-MM-yyyyisn't an ISO format. There are only 2 ISO formatsyyyyMMdd(style code 112) andyyyy-MM-ddThh:mm:ss.sssssss(style code 126). In SQL Server, these are the only 2 string literal formats that are completely unambiguous regardless of language and data type. Evenyyyy-MM-ddis not ambiguous, as it is interpreted differently when using the(small)datetimedatatype to the other date(time) datatypes.Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thanks for both useful code and careful explaining.
- Thom A wrote:
Even
yyyy-MM-ddis not ambiguous, as it is interpreted differently when using the(small)datetimedatatype to the other date(time) datatypes.I do not understand this. Is it 'not ambiquous'/'ambiguous'/'not unambiquous'.
I do get the 'same' result independ of the datatypes. See:
select CONVERT(smalldatetime, '1904-02-03') The_Date, 'A' Example
Union all
select CONVERT(datetime, '1904-02-03'), 'B'
Union all
select CONVERT(datetime2, '1904-02-03'), 'C'
Union all
select CONVERT(date, '1904-02-03'), 'D'
The_Date Example
---------------------- -------
1904-02-03 00:00:00.00 A
1904-02-03 00:00:00.00 B
1904-02-03 00:00:00.00 C
1904-02-03 00:00:00.00 D
(4 row(s) affected)Running the four queries independently give different formats, but all for the same data.
Ben
- ben.brugman wrote:Thom A wrote:
Even
yyyy-MM-ddis not ambiguous, as it is interpreted differently when using the(small)datetimedatatype to the other date(time) datatypes.I do not understand this. Is it 'not ambiquous'/'ambiguous'/'not unambiquous'.
Something that is ambiguous means that it's value cannot be determined with certainty. Take the date "05/06/07"; it has many possible values. It could be interpreted as any of the following values:
- 05 June 2007
- 06 May 2007
- 07 June 2005
- 06 July 2005
Literal String dates in SQL Server are exactly the same, and there are only 2 formats that will be correctly converted to the right date, 100% of the time, regardless of the datatype and user's language. Those 2 types are
yyyyMMddandyyyy-MM-ddThh:mm:ss.sssssss.As I said,
yyyy-MM-ddis not ambiguous because(small)datetimetreats the value different, and can give the wrong result. For me, If I use the following statement:SELECT CONVERT(date,'2018-04-03'),
CONVERT(datetime,'2018-04-03');The results I get are 03 April 2018 and 04 March 2018. As a result if I tried
CONVERT(datetime,'2018-04-29')I'd get a conversion error.Because you have no idea what language SQL might be run in, it's really important to try and make it so that dates are ambiguous; as otherwise you could get unexpected results.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thank you Thom,
In my configurations that example gave the 'same' results. (But yes that depends on the regional/language settings. Didn't notice that in my examples
).Ben
Why do Americans have a so ill-logical date notations, was the furlong–firkin–fortnight (FFF) system instead of metric not enough for them, they also have to mess up the date notation ?
- Thom A wrote:
As I said,
yyyy-MM-ddisnotambiguous because(small)datetimetreats the value different, and can give the wrong result. For me, If I use the following statement:The confusion is that you keep contradicting yourself. I've corrected it for you.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen wrote:Thom A wrote:
As I said,
yyyy-MM-ddisnotambiguous because(small)datetimetreats the value different, and can give the wrong result. For me, If I use the following statement:The confusion is that you keep contradicting yourself. I've corrected it for you. Drew
D'oh! Why my signature says what it does I suppose.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thom - these issues are related to datetime/smalldatetime data types. The format YYYY-MM-DD is a valid ISO date format but is not handled correctly in SQL Server when using either data type.
However - when using date/datetime2 data types the ISO format YYYY-MM-DD is correctly interpreted.
Additionally, ISO states in an addendum that 'YYYYMMDD HH:MM:SS.nnn' and 'YYYY-MM-DD HH:MM:SS.nnn' can be considered a valid format *if both parties agree* (paraphrasing here). Again - the problem here is that SQL Server can interpret 'YYYY-MM-DD HH:MM:SS.nnn' incorrectly, but datetime2 will interpret it correctly (and yes, I am aware of the performance issues related to datetime2).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply