my login language is set to English

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

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

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

this is only a development environment so its not an issue, but id like to change it for the entire server , not just login specific so that its the...

this converts as null on mine

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

date format is dmy
language is english (united states) although it should really be english UK, can I change this ?

Ive asked if its possible to have consistent date formats in the csv file, but its exported from an external system so Im not sure if its possible, just waiting...

the problem is , this data is being inserted into a master table and it doesnt allow null date fields (the source is imported from a csv which should also...

the try-cast method worked, although it seemed to stop any folllowing commands from executing (I had multiple options in the case statement)
So I made it the last option in...

I dont have a StringTime datatype.  The reason its a varchar is because the data is imported into a temporary holding table from a .csv file which will subsequently be...

these are all the string values in the table, none of them are out of time conversion range

11:00:00.0000000
19:00:00.0000000
23:00:00.0000000
13:00:00.0000000

ok, cool Ill change it to a tvf, I just wanted something quick and dirty while im testing and developing this bit of functionality, but thanks for the heads up...

the underlying field is varchar(50)

Ive tried this

WHEN EndTime LIKE '[0-9][0-9][0-9][0-9]' THEN LEFT(dbo.RemoveNonNumericChar(EndTime),2) + ':' + RIGHT(dbo.RemoveNonNumericChar(EndTime),2) + ':00.0000000'

and this gives me a string (and ALL the other values give me...