Error Message Msg 242, Level 16, State 3, Line 26 The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
select cast(convert(nvarchar(10), getdate(), 105) as datetime) as startTime
Next question is why is 105 (passed as the style argument) ?
From Cast and Convert – Transact SQL ( http://msdn.microsoft.com/en-us/library/ms187928.aspx ), here are the “Date Format Styles”:
What is our Session’s Date Format
Get Current Session’s Date Format
Crediting Scott Munro – “Setting and resetting the DATEFORMAT in SQLServer 2005″( http://stackoverflow.com/questions/305462/setting-and-resetting-the-dateformat-in-sqlserver-2005 )
select date_format from sys.dm_exec_sessions where session_id = @@spid;
So it appears we have a mismatch between the code which is expecting to get back a date formatted for 105 (dmy), European, and my SQL Server’s login\session default of mdy.
Let us put in a temporarily fix for this session, by issuing a “set dateformat dmy;”
-- set session's date format to dmy (day-month-year = european) set dateformat dmy; select cast(CONVERT(NVARCHAR(10), getdate(), 105) as datetime) as startTime
In our case, our permanent fix will be to create a dedicated Login for our application and assign an European language to it. Here is the code line for changing our Login's language to British English.
ALTER LOGIN [appLogin] WITH DEFAULT_LANGUAGE=[British] GO
- Alter Login Date Format does not exist; changing the default language applies to the date format, as well
- Please do not be swayed by Vendors insisting that they have to run as sa
List Available Languages
To get an inventory of available languages, query syslanguages.
For our specific use-case, we are only interested in US and British English.
select tblSL.[langid] , tblSL.[dateformat] , tblSL.upgrade , tblSL.name , tblSL.alias from syslanguages tblSL where tblSL.alias in ('English', 'British English') ;
SQL Server Profiler
How does SQL Server effect default language settings? Glad you asked.
Let us follow along using SQL Server Profiler:
- Connection Pooling
- We can quickly digest that “Connection Pooling” plays a good, hidden role in how things work these days
- Gaining connections is expensive and as such SQL Server will try to re-use existing connections
- Unfortunately in this case the login’s language has changed and so SQL Server is forced to create new connection
- Also, the Application does not have to issue a set date format, as sql server does so on behalf on the user
Dedicated to Sierra Leona born Medical Doctor, Martin Salia, who is fighting a far more worthy battle.
Ebola has really brought to light the transcendental work of communities of faiths such as “Pan African Academy of Christian Surgeons” (PAACS) – https://www.medicalmissions.com/network/organizations/pan-african-academy-of-christian-surgeons.
Sorry used up a lot of ink, thank God, no paper, to try to shed light on a little obscure topic.