October 2, 2018 at 5:24 am
Dear all - We successfully moved a DB from a 2008 platform to 2012. However we received reports from our Web Team to advise one of their scripts wasn't running successfully due to date format issues.
The error being returned is
Msg241, Level 16, State 1, Line 1
when running the following:
INSERTINTO CCSS.dbo.officer_holidays ([officer_id],[start_date],[end_date],[start_time],[end_time]) VALUES (4, '23/06/2017', '26/06/2017', '09:15', '17:00')
The Server is set to us_english & the SQL account used to run the script is set to British which is correct but still returns the above error.
Does anyone have any ideas to resolve this, as I understand you cannot change the language setting at the DB level & I'm reluctant to change this at the Server level as I'm not sure what impact this will have on other DBs that reside on the same Server.
Any help is very much appreciated.
Many thanks
Dax
October 2, 2018 at 5:32 am
Are you sure the languages are configured correctly? I'm on a Server right now with the language set to "English" (American), but my login's language is set to British and the query SELECT CONVERT(date,'26/06/2017'); runs without error.
I would strongly suggest that the language of the login is not set to British, or the person isn't using the login you/they think they are.
Also, that isn't the full error, you've missed out one of the lines. I suspect the full error is something like:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 2, 2018 at 8:24 am
Hi Thom - Many thanks for your reply, please see below:
select @@LANGUAGE returns us_english
select default_language_name
from sys.sql_logins
where name = 'webuser_ccss' This returns a value of british
SELECT
CONVERT(date, '26/06/2017'); This returns an error as shown below as you've already guessed in your reply:
Msg 241,Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Thanks again & any ideas?
Dax
October 2, 2018 at 10:35 am
You could change the date format just for that code:
DECLARE @dateformat_setting char(3)
IF OBJECT_ID('tempdb.dbo.#useroptions') IS NOT NULL
DROP TABLE #useroptions
CREATE TABLE #useroptions (
set_option varchar(128) NULL,
value varchar(8000) NULL
)
INSERT INTO #useroptions
EXEC('DBCC USEROPTIONS')
--capture the current dateformat setting
SELECT @dateformat_setting = value
FROM #useroptions
WHERE set_option = 'dateformat'
--change dateformat to allow custom INSERT here
SET DATEFORMAT DMY
INSERT INTO CCSS.dbo.officer_holidays ([officer_id],[start_date],[end_date],[start_time],[end_time])
VALUES (4, '23/06/2017', '26/06/2017', '09:15', '17:00')
--restore original dateformat setting
SET DATEFORMAT @dateformat_setting
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 2, 2018 at 12:12 pm
@@language should be British if you're on a login with the language set to British (@@LANGUAGE returns the language of the currentc session). The fact that it's not proves my suspicion. Change the language of the session, or the format of your dates.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply