June 24, 2019 at 12:02 pm
Hello,
Our new serer SQL 2016 on Windows 2019 is set to language British English. My login is set to British English.
Then why is the count of records showing different when using this date format:
>= 2018-08-01
It's giving records total from 8th January instead of 1st August.
I am comparing the same query with SQL 2008 R2 and it's behaving okay. Total records on both servers for the table are exactly same.
Why is SQL 2016 using yyyy-dd-mm format?
How to change that the server level?
Many thanks
Vinay
June 24, 2019 at 12:19 pm
Use '20180801' instead of '2018-08-01'
If the columns on your table were datetime2 then it would also correctly convert the format you are using.
set language british
/* CORRECT CONVERSION */
select convert(datetime,'2018-08-01T00:00:00')
select convert(datetime2,'2018-08-01')
select convert(datetime,'20180801')
/* INCORRECT CONVERSION */
select convert(datetime,'2018-08-01')
select convert(datetime,'2018-08-01 00:00:00')
Or better still just set the language of your login to 'English' instead of British.
June 24, 2019 at 12:45 pm
Jonathan,
Is this a change in SQL2016? Because the format yyyy-mm-dd behaves perfectly fine in SQL 2008 R2.
I ran your set of correct/incorrect ones and the output changes to yyyy-dd-mm in SQL 2016.
Many thanks,
Vinay
June 24, 2019 at 12:47 pm
yyyy-MM-dd didn't work in 2008 either, if the data type was datetime. It will be interpreted as yyyy-dd-MM for datetime using the British language in both versions. Perhaps you were using a different data type, like date or datetime2, before?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 24, 2019 at 1:16 pm
Jonathan, Is this a change in SQL2016? Because the format yyyy-mm-dd behaves perfectly fine in SQL 2008 R2. I ran your set of correct/incorrect ones and the output changes to yyyy-dd-mm in SQL 2016. Many thanks, Vinay
Sorry, I don't have access to any 2016 boxes at the moment.
I would just change the language of the login to from British to English to fix it though.
June 24, 2019 at 1:22 pm
I would just change the language of the login to from British to English to fix it though.
Is that really the solution? Applications, and code, should be language ambiguous. Changing the language for the OP doesn't fix the problem and more "hides" it; and it could introduce other breaking changes (what if the OP is using dates in the format dd/MM/yyyy else where?). If they have multiple users then they have to change the language for all the users, which could be a even bigger problem (especially if they have non-English speaking users).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 24, 2019 at 1:39 pm
Jonathan AC Roberts wrote:I would just change the language of the login to from British to English to fix it though.
Is that really the solution? Applications, and code, should be language ambiguous. Changing the language for the OP doesn't fix the problem and more "hides" it; and it could introduce other breaking changes (what if the OP is using dates in the format
dd/MM/yyyyelse where?). If they have multiple users then they have to change the language for all the users, which could be a even bigger problem (especially if they have non-English speaking users).
Well that would make me ask: what is the point of having a language setting?
I think you would probably break most database systems if you went and changed the language to every language available. I see hard-coded 'yyyy-mm-dd' in a lot of code, so if you changed the language to British in any of those bits of code it would probably break the code. I've always just steered clear of using British as a language setting.
June 24, 2019 at 1:52 pm
Well that would make me ask: what is the point of having a language setting? .
Not everyone speaks English. If every error message was in English, and things like CONVERT only used English for date style codes that would be a "bit" of a problem for a lot of users. Why not just make Windows available in English going forward, and force the whole word to speak one language?
Languages are there for a reason, and they do have impacts on "simple" things such as the date here. The important part is that you code appropriately and therefore when using dates you should be using an ambiguous format. For SQL Server, that's yyyyMMdd and yyyy-MM-ddThh:mm:ss.sssssss (regardless of data type and language).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 24, 2019 at 1:58 pm
Languages are there for a reason, and they do have impacts on "simple" things such as the date here. The important part is that you code appropriately and therefore when using dates you should be using an ambiguous format. For SQL Server, that's
yyyyMMddandyyyy-MM-ddThh:mm:ss.sssssss(regardless of data type and language).
I agree you should be using unambiguous date formats, but maybe you inherit some old code that isn't using them. But I would just not use British as the language setting in the first place and use English instead.
June 24, 2019 at 2:21 pm
Thom A wrote:Languages are there for a reason, and they do have impacts on "simple" things such as the date here. The important part is that you code appropriately and therefore when using dates you should be using an ambiguous format. For SQL Server, that's
yyyyMMddandyyyy-MM-ddThh:mm:ss.sssssss(regardless of data type and language).I agree you should be using unambiguous date formats, but maybe you inherit some old code that isn't using them. But I would just not use British as the language setting in the first place and use English instead.
But why should the OP (who I assume is British) and I be forced to use the wrong language? According to Microsoft I don't speak English (despite living in England) I speak British or "English international". Why shouldn't all American's use English going forwards instead? (And by English, I mean English, not American.) The languages are different, and so the users will user the language appropriate for them.
For a specific Service account, yes you can use a different language. I actually had that very problem today, as when i asked the vendor on Friday "What language does the application need, or is it language ambiguous?" they respodned "Use the default language.", so I said "Ok, I've set it to our default of BRITISH". Had an email this morning "Hi, there's 1,000's of errors about cannot convert varchar value to datetime?" /facepalm
But if it's a user account then give the user the language appropriate to them. Like I said, I don't speak American, I speak English, and so my Language setting (on SQl Server) is BRITISH, as are all of our user accounts (and apart from that now one service account, all the other logins).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 24, 2019 at 2:36 pm
Jonathan/Thom,
You both are right actually. We have many scripts with dates in dd/mm/yyyy and yyyy-mm-dd format. Plus we have to change few staff members login to English login instead of British English.
What is strange is that why the dash makes a difference in as yyyy-mm-dd to yyyymmdd.
Is this not considered an issue with date handling in MS-SQL? If mm is put in between century and day, then I would expect SQL to treat it as a Month all the time regardless of a dash.
Thank you both.
Vinay
June 24, 2019 at 2:41 pm
What is strange is that why the dash makes a difference in as yyyy-mm-dd to yyyymmdd. Is this not considered an issue with date handling in MS-SQL?
It's specifically a problem when using datetime. For date, datetime2 and the other "newer" date/time datatypes yyyy-MM-dd is unambiguous, however, with datetime, it is not.
If you have a look at this DB<>Fiddle, you'll notice in the first query that the datetime fails the convert, and in the second returns a different value. All the others work as "expected".
If you aren't storing a time portion in your dates, then using the date datatype would also "solve" the issue, as yyyy-MM-dd would be unambiguous and (as a bonus) it's also a smaller datatype.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 24, 2019 at 3:10 pm
Jonathan/Thom, You both are right actually. We have many scripts with dates in dd/mm/yyyy and yyyy-mm-dd format. Plus we have to change few staff members login to English login instead of British English. What is strange is that why the dash makes a difference in as yyyy-mm-dd to yyyymmdd. Is this not considered an issue with date handling in MS-SQL? If mm is put in between century and day, then I would expect SQL to treat it as a Month all the time regardless of a dash. Thank you both. Vinay
yyyymmdd will always work, yyyy-mm-dd is only ISO compliant with the T and the time component. You can also use yyyyMMddThh:mm:ss.000 as a non-breakable format.
There are other differences in the language setting, for example, English the start of the week (day 1) is Sunday, British it's Monday. For the industry I'm involved with Sunday is the first day of the week, so English is a more appropriate language for me:
set language british;
select datepart(weekday,getdate())
set language english;
select datepart(weekday,getdate())
Personally, I would never use any format like dd/mm/yyy or mm/dd/yyyy in any script.
In addition to SET LANGUAGE, there is also SET DATEFORMAT which makes a difference to the input and also displayed output to the screen.
I'm really surprised if it really is the case that Microsoft have changed the behaviour of something as basic as this when going to a different version of SQL Server.
June 24, 2019 at 3:40 pm
Just wanted to add that
sp_helplanguage british;
sp_helplanguage english;
will show the differences between languages. With
EXEC sp_configure 'default language', 'British English';
you could change the default language for the Server to BE like that BUT
unless you change the language for the logins, too this might not have the expected effect.
So you can either enjoy changing Server and Login settings or work with
SET LANGUAGE British;
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply