weird string problem

Question

weird string problem

Viewing 7 posts - 31 through 36 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99127 More actions · Answer 1

solus - Friday, May 12, 2017 8:34 AM
Ive just executed these two commands and restarted the server, but its still showing language as English (United States)
SET LANGUAGE British
go
set DATEFORMAT dmy
go

Those statements only change it for that session. if you were to run a query in another query window, your language will default.

The way to change your language on your LOGIN is detailed in my prior post. you could, however use the SET language to see if there are any other problematic rows:
SET LANGUAGE British; GO

SELECT START_DATE FROM [Yourtable] WHERE TRY_CAST(START_DATE AS date) IS NULL;

SELECT CONTRACT_DATE FROM [Yourtable] WHERE TRY_CAST(CONTRACT_DATE AS date) IS NULL;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

solus Hall of Fame Points: 3495 More actions · Answer 2

Im using windows authentication while developing not sql authentication, is there a way to change this permanently for the whole server ? I want it to be British anyway

solus Hall of Fame Points: 3495 More actions · Answer 3

this works
SELECT TRY_CAST('10/18/2016 00:00' AS date)

I get
2016-10-18

so sql server still thinks its US format

Thom A SSC Guru Points: 99127 More actions · Answer 4

solus - Friday, May 12, 2017 8:55 AM
this works
SELECT TRY_CAST('10/18/2016 00:00' AS date)
I get
2016-10-18
so sql server still thinks its US format

Have you changed your login's language?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

solus Hall of Fame Points: 3495 More actions · Answer 5

solus

Hall of Fame

Points: 3495

May 15, 2017 at 3:32 am

#1942490

my login language is set to English

Thom A SSC Guru Points: 99127 More actions · Answer 6

solus - Monday, May 15, 2017 3:32 AM
my login language is set to English

So, again, this is why 18/10/17 is failing, there are not 18 months in a year. English in SQL Server is not really English, it's American. British is English (honestly, as someone who is English, it really grates me that English is not called English in a lot of applications). If all your dates are in d/m/y format, then you need to set your login's language, or your query's Session language to British, which I have described how to do in my previous posts.

Also, as I warned previously, if all your dates are in d/m/y format and are you are using the English language, CAST('05/06/2017' AS date) will result in 2017-05-06 (6th May 2017) NOT 2017-06-05 (5th June 2017). This likely means the majority of your dataset is currently wrong, if you have imported any data already (apart from when the day and month are the same number, i.e. 02/02/2017).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

solus Hall of Fame Points: 3495 More actions · Answer 7

using this

TRY_PARSE(START_DATE AS datetime using 'en-GB') AS START_DATE,

fixed the problem