April 25, 2019 at 7:56 am
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
April 25, 2019 at 8:06 am
Instead of 112 you need 105
SELECT CONVERT(DATE,MAX(dato),105) FROM dbo.dutyrostershift WHERE employeeid = 11
April 25, 2019 at 8:35 am
Instead of 112 you need 105
You need to change the data type too (to varchar), as the date datatype 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 a varchar to enforce a "format". Note that as soon as you convert the value to a varchar it 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-yyyy isn't an ISO format. There are only 2 ISO formats yyyyMMdd (style code 112) and yyyy-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. Even yyyy-MM-dd is not ambiguous, as it is interpreted differently when using the (small)datetime datatype to the other date(time) datatypes.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 25, 2019 at 8:50 am
Thanks for both useful code and careful explaining.
April 29, 2019 at 6:56 am
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
April 29, 2019 at 7:56 am
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:
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 yyyyMMdd and yyyy-MM-ddThh:mm:ss.sssssss.
As I said, yyyy-MM-dd is not ambiguous because (small)datetime treats 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
April 29, 2019 at 1:46 pm
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 ?
April 30, 2019 at 3:32 pm
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
April 30, 2019 at 3:38 pm
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
April 30, 2019 at 6:01 pm
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply