sql-servert-sqlsql-server-2016-express

Sql Server Language Settings - Preserve UK Date setting in view


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?


Solution

  • 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