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?
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