0

I am trying to convert string date/times from existing data to T-SQL DATETIME type. The strings are in UK format 'dd/MM/yyyy HH:mm:ss' 

CREATE VIEW v_DateFix
AS
   SELECT CAST([Start Date Local] AS DATETIME) AS StartDate
   FROM MyTable
GO

This initially fails: Error message is

Msg 242, Level 16, State 3, Line 39
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

So I reconfigured the language settings

select * 
from sys.syslanguages 
where name = 'British'

-- Set default language to British 
EXEC sp_configure 'default language', 23;
GO

RECONFIGURE;
GO

Create View still throws the same error. I tried configuring the default language on master too, and restarting the server. The view still cannot be created.

I can use this statement prior to the CREATE VIEW statement

SET LANGUAGE BRITISH;

Now the view can be created. However restarting the server or even opening a new query window and selecting from the view still gives the error. I can't include the SET LANGUAGE statement as part of the view.

So how can I get SQL Server Express V 14.0.1000 to always use UK date formats?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
TomFp
  • 469
  • 1
  • 5
  • 13
  • 1
    Language settings are a connection/login setting; you can't change the current login's preferences in a `VIEW`. To ensure things always work, regardless of language, then use the ISO formats: `yyyyMMdd` and `yyyyMMddThh:mm:ss.sss`. – Thom A Apr 26 '18 at 09:27
  • it sounds like, however, that you're storing a date(time) value as a `varchar`. As you've learned, don't. Change the datatype of your column within your table to the correct data type and you won't have this problem in the first place. :) – Thom A Apr 26 '18 at 09:30
  • Hint: Do not change the default language. This can have various side effects! Localisation is something to be done in the front-end... – Shnugo Apr 26 '18 at 09:37
  • Thanks for the login preferences info. Is. And yes Ideally the values should have been converted before storing, however I have to work with data provided by a 3rd party – TomFp Apr 26 '18 at 09:41

1 Answers1

1

You can always use something like this:

SELECT CONVERT(DATETIME, '01/06/2018 09:30:15', 103)

The 103 indicates a format, as described here

HoneyBadger
  • 14,750
  • 3
  • 34
  • 48
  • Not only **can**, but rather **must**... Any approach with `CAST` relys on the system's culture implicitly. The same appears with `CONVERT` without the third parameter. – Shnugo Apr 26 '18 at 09:39
  • @Shnugo So would you recommend leaving an instance of SQL Server to be left as default 'US English' culture with its MDY date format, even when installed on Windows set to UK culture, and only for use in the UK – TomFp Apr 26 '18 at 09:52
  • @TomFp For a fresh installation you can set whatever you want... But if there is a lot going on already, don't change the defaults... The general rule is: **Never rely on defaults!** Use the correct types for your columns, use commands wtih explicit format specification and try to take the "visible" format as something not important for **within** the database. This is for data import and output only... – Shnugo Apr 26 '18 at 10:01