0

An application is passing the below query to the SQL server and I'm receiving an exception from SQL server as The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

update Images set  Created_DATE='23/09/2020 11:00:09'
 where ID = 10

Additionally, I cant see the below error in Profiler.

enter image description here

What I've tried is,

  1. Changes the Date format of SQL server as DMY
  2. Change the language to en-GB

I can't change the code so how to make this work by changing SQL server configuration?

SᴇM
  • 7,024
  • 3
  • 24
  • 41
Jay
  • 41
  • 7
  • You do not need that double quotes. – Roman Ryzhiy Sep 23 '20 at 06:02
  • And use the convert function if still required. – Dale K Sep 23 '20 at 06:03
  • If you can’t change the code and is about SQLServer configuration, why is this tagged as C# and .NET? – ckuri Sep 23 '20 at 06:54
  • First thing I would do, to ask people who wrote that application, to pass the date as `DateTime`, not `string`. – SᴇM Sep 23 '20 at 07:54
  • If you have no way to change code, you can do several things, first thing comes in mind, is... for example if you have a table called MyTable, then create table MyTable_2 (name it whatever you prefer), create a trigger on MyTable insert and convert that `string` date to `datetime` and insert it to MyTable_2. – SᴇM Sep 23 '20 at 08:00

1 Answers1

0

Try this:

update Images 
set  Created_DATE=CONVERT(DATETIME, N'23/09/2020 11:00:09', 103) 
where ID = 10

You need to convert the string to date setting a culture. Otherwise, the engine is not able to understand the format as there are various formats.

I believe, your date type is DATETIME, and that's why you are getting this error:

SELECT CAST('23/09/2020 11:00:09' AS DATETIME);

result as:

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

for DATETIME2 it is:

Msg 241, Level 16, State 1, Line 4 Conversion failed when converting date and/or time from character string.

gotqn
  • 42,737
  • 46
  • 157
  • 243
  • The problem is the query is coming from an application and I can't change the application code. Can it be handled in SQL server by making some config changes or by triggers? – Jay Sep 23 '20 at 06:06
  • @Jay, No, you cannot use a trigger. You can change the default language - https://stackoverflow.com/a/15706057/1080354 – gotqn Sep 23 '20 at 06:16
  • It is British English but still getting the error though the dateformat of British English is D/M/Y – Jay Sep 23 '20 at 06:19
  • So, if you execute the code as I have shown in the answer in the SQL Server Management Studio you are still getting an error? – gotqn Sep 23 '20 at 06:21
  • I know the convert function will make this work. My question is I cant change the query as it comes from application and wants to make this work by changing culture language etc.. – Jay Sep 23 '20 at 06:28
  • @Jay, I mean, can you test this? `SET LANGUAGE British; SELECT CAST(N'23/09/2020 11:00:09' AS DATETIME); ` – gotqn Sep 23 '20 at 06:30
  • This works in SSMS but when I get that query from application, it throws an error – Jay Sep 23 '20 at 06:32
  • Any thoughts, i can see the cast function works after changing the language in SSMS. But when query comes from external application it still fails with the same error – Jay Sep 23 '20 at 06:34
  • 1
    Well, the application code itself can be setting a language. You can create a trigger to record the current language using `SELECT @@language` and check if your database settings is overwritten. Also, in the SSMS, if you just execute in a new query window `SELECT CAST(N'23/09/2020 11:00:09' AS DATETIME);` - is this still working? – gotqn Sep 23 '20 at 06:41
  • It looks like application not overwriting anything and cast query still works in SSMS. But in profiler I can see an error message an shown in the question. Could that be a problem? – Jay Sep 23 '20 at 06:44
  • I can see the below in profiler when I start.Later it tries to change to british english it seems....///////// set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 – Jay Sep 23 '20 at 06:48
  • @Jay, the profiler is showing this as error but it is not - `It looks like all infoMessage tokens are handled as User Error Message in SQL Profiler.` https://github.com/tediousjs/tedious/issues/101 – gotqn Sep 23 '20 at 07:21
  • Also in profiler i can see the language changing automatically. It changes after exec sp_reset_connection and it is automatically switching from English to British English and Vice versa. As a result when application send the query the language changes to English and query fails – Jay Sep 23 '20 at 07:24
  • Just for the test, can you change the column data type to `VARCHAR(18)` and see how exactly the sent value looks like. – gotqn Sep 23 '20 at 07:26
  • It comes as 23/09/2020 13:01:55 – Jay Sep 23 '20 at 07:31