Xjyuk

C++20 with u8, char8_t and std::string

What verb goes with "coup"?

How come having a Deathly Hallow is not a big deal?

What can a novel do that film and TV cannot?

Wrong Output in self defined Quaternionic Multiplication

How can I know (without going to the station) if RATP is offering the Anti Pollution tickets?

Term for a character that only exists to be talked to

How long had Bertha Mason been in the attic at the point of the events in Jane Eyre

Why did moving the mouse cursor cause Windows 95 to run more quickly?

Magento 2: I am not aware about magneto optimization. Can you please share the steps for this?

Birthday girl's casino game

How frequently do Russian people still refer to others by their patronymic (отчество)?

Will greasing clutch parts make it softer

Misaligned Columns

Do I need to be legally qualified to install a Hive smart thermostat?

When you're given a degree sequence, what is the method to draw a graph which has that degree sequence?

Isn't "Dave's protocol" good if only the database, and not the code, is leaked?

When should we use dependency injection (C#)

gzip compress a local folder and extract it to remote server

Do the 26 richest billionaires own as much wealth as the poorest 3.8 billion people?

Why would a propellor have blades of different lengths?

Why did my leaking pool light trip the circuit breaker, but not the GFCI?

Who are the police in Hong Kong?

Did Winston Churchill praise Rolls-Royce engines?

SQL Server error 242 with ANSI datetime

Zero Rows return on querying DMV sys.dm_os_performance_countersUnable to restore TDE enabled database when MAXTRANSFERSIZE and CHECKSUM is usedHow to create SOAP endpoint in SQL Server 2016?Simple insert in stored procedure runs twiceInconsistent answers to simple queriesHow to insert into TABLE Variable?Exporting to Excel from SQL Server - BCP Using a Stored Procedure Headers not shownSQL Server Agent stopped - Event ID 17052Error while trying to create a database for SSRSWhy SQL Server Standard 2017 is using more memory than limit described in documentation?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

4

I'm trying to debug a MS SQL Server error 242:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The error is originated by this statement:

CREATE TABLE db.schema.test (
 Column1 datetime NULL
) GO

INSERT INTO db.schema.test (Column1)
VALUES (convert(varchar,convert(datetime,D '2019-06-30'),102));

This is my server version:

Microsoft SQL Server 2017 (RTM-CU15) (KB4498951) - 14.0.3162.1 (X64) 
 May 15 2019 19:14:30 
 Copyright (C) 2017 Microsoft Corporation
 Standard Edition (64-bit) on Linux (Debian GNU/Linux 9 (stretch))

And the language @@LANGUAGE is Italiano.

The same statements works on

Microsoft SQL Server 2017 (RTM-CU10) (KB4342123) - 14.0.3037.1 (X64)
 Jul 27 2018 09:40:27
 Copyright (C) 2017 Microsoft Corporation
 Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor) 

With language: us_english

Without without making explicit convert style it works

INSERT INTO db.schema.test (Column1)
VALUES (convert(varchar,convert(datetime,D '2019-06-30')));

Convert style 102 is ANSI yyyy.mm.dd, shouldn't be recognized by both English and Italian SQL Server?

sql-server datetime type-conversion

share|improve this question

edited 7 hours ago

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

asked 8 hours ago

sgargelsgargel

20011 silver badge1313 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Are you converting from char to datetime to varchar? Try with: INSERT INTO dbo.schema.test (Column1) VALUES CAST('20190630' as datetime)
  
  – McNets
  8 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @McNets this works: INSERT INTO db.schema.test (Column1) VALUES (CAST('20190630' as datetime)); this not [err 242] INSERT INTO db.schema.test (Column1) VALUES (CAST('2019-06-30' as datetime));
  
  – sgargel
  8 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Try by using / instead of -, or by using your regional settings (dd-mm-yyyy I suppose)
  
  – McNets
  8 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  @McNets Comments are not for guessing.
  
  – Paul White♦
  7 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  mssqltips.com/sqlservertip/5206/…
  
  – Aaron Bertrand♦
  4 hours ago
  

  
  
  
  

add a comment | 

4

I'm trying to debug a MS SQL Server error 242:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The error is originated by this statement:

CREATE TABLE db.schema.test (
 Column1 datetime NULL
) GO

INSERT INTO db.schema.test (Column1)
VALUES (convert(varchar,convert(datetime,D '2019-06-30'),102));

This is my server version:

Microsoft SQL Server 2017 (RTM-CU15) (KB4498951) - 14.0.3162.1 (X64) 
 May 15 2019 19:14:30 
 Copyright (C) 2017 Microsoft Corporation
 Standard Edition (64-bit) on Linux (Debian GNU/Linux 9 (stretch))

And the language @@LANGUAGE is Italiano.

The same statements works on

Microsoft SQL Server 2017 (RTM-CU10) (KB4342123) - 14.0.3037.1 (X64)
 Jul 27 2018 09:40:27
 Copyright (C) 2017 Microsoft Corporation
 Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor) 

With language: us_english

Without without making explicit convert style it works

INSERT INTO db.schema.test (Column1)
VALUES (convert(varchar,convert(datetime,D '2019-06-30')));

Convert style 102 is ANSI yyyy.mm.dd, shouldn't be recognized by both English and Italian SQL Server?

sql-server datetime type-conversion

share|improve this question

edited 7 hours ago

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

asked 8 hours ago

sgargelsgargel

20011 silver badge1313 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Are you converting from char to datetime to varchar? Try with: INSERT INTO dbo.schema.test (Column1) VALUES CAST('20190630' as datetime)
  
  – McNets
  8 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @McNets this works: INSERT INTO db.schema.test (Column1) VALUES (CAST('20190630' as datetime)); this not [err 242] INSERT INTO db.schema.test (Column1) VALUES (CAST('2019-06-30' as datetime));
  
  – sgargel
  8 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Try by using / instead of -, or by using your regional settings (dd-mm-yyyy I suppose)
  
  – McNets
  8 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  @McNets Comments are not for guessing.
  
  – Paul White♦
  7 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  mssqltips.com/sqlservertip/5206/…
  
  – Aaron Bertrand♦
  4 hours ago
  

  
  
  
  

add a comment | 

I'm trying to debug a MS SQL Server error 242:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The error is originated by this statement:

CREATE TABLE db.schema.test (
 Column1 datetime NULL
) GO

INSERT INTO db.schema.test (Column1)
VALUES (convert(varchar,convert(datetime,D '2019-06-30'),102));

This is my server version:

Microsoft SQL Server 2017 (RTM-CU15) (KB4498951) - 14.0.3162.1 (X64) 
 May 15 2019 19:14:30 
 Copyright (C) 2017 Microsoft Corporation
 Standard Edition (64-bit) on Linux (Debian GNU/Linux 9 (stretch))

And the language @@LANGUAGE is Italiano.

The same statements works on

Microsoft SQL Server 2017 (RTM-CU10) (KB4342123) - 14.0.3037.1 (X64)
 Jul 27 2018 09:40:27
 Copyright (C) 2017 Microsoft Corporation
 Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor) 

With language: us_english

Without without making explicit convert style it works

INSERT INTO db.schema.test (Column1)
VALUES (convert(varchar,convert(datetime,D '2019-06-30')));

Convert style 102 is ANSI yyyy.mm.dd, shouldn't be recognized by both English and Italian SQL Server?

sql-server datetime type-conversion

share|improve this question

edited 7 hours ago

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

asked 8 hours ago

sgargelsgargel

20011 silver badge1313 bronze badges

CREATE TABLE db.schema.test (
 Column1 datetime NULL
) GO

INSERT INTO db.schema.test (Column1)
VALUES (convert(varchar,convert(datetime,D '2019-06-30'),102));

Microsoft SQL Server 2017 (RTM-CU15) (KB4498951) - 14.0.3162.1 (X64) 
 May 15 2019 19:14:30 
 Copyright (C) 2017 Microsoft Corporation
 Standard Edition (64-bit) on Linux (Debian GNU/Linux 9 (stretch))

Microsoft SQL Server 2017 (RTM-CU10) (KB4342123) - 14.0.3037.1 (X64)
 Jul 27 2018 09:40:27
 Copyright (C) 2017 Microsoft Corporation
 Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor) 

INSERT INTO db.schema.test (Column1)
VALUES (convert(varchar,convert(datetime,D '2019-06-30')));

share|improve this question

edited 7 hours ago

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

asked 8 hours ago

sgargelsgargel

20011 silver badge1313 bronze badges

share|improve this question

edited 7 hours ago

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

asked 8 hours ago

sgargelsgargel

20011 silver badge1313 bronze badges

edited 7 hours ago

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

edited 7 hours ago

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

asked 8 hours ago

sgargelsgargel

20011 silver badge1313 bronze badges

asked 8 hours ago

sgargelsgargel

20011 silver badge1313 bronze badges

1 Answer
1

active

oldest

votes

8

VALUES (convert(varchar,convert(datetime,D '2019-06-30'),102));

Starting from the inside, the ODBC escape sequence D '2019-06-30' returns a datetime.

You're then converting that to a string with 102 style (rather than 105 for Italian).

You're then relying on an implicit conversion back to datetime to match the type of the target column.

The implicit conversion has a default style of 0 as you can see in the execution plan:

[Expr1003] = Scalar Operator(CONVERT_IMPLICIT(datetime,CONVERT(varchar(30),[@1],102),0))

(note: you should always specify the maximum length when using varchar)

When you use style 102 yyyy.mm.dd you must also set DATEFORMAT to YMD so SQL Server can parse the format under style 0.

When you use style 105 dd-mm-yyyy, you must set DATEFORMAT to DMY for the same reason.

The reason it works on one and not the other is the default DATEFORMAT for the language in each case.

See datetime and SET LANGUAGE.

share|improve this answer

edited 7 hours ago

answered 7 hours ago

Paul White♦Paul White

56.9k1414 gold badges298298 silver badges472472 bronze badges

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f241799%2fsql-server-error-242-with-ansi-datetime%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

8

VALUES (convert(varchar,convert(datetime,D '2019-06-30'),102));

Starting from the inside, the ODBC escape sequence D '2019-06-30' returns a datetime.

You're then converting that to a string with 102 style (rather than 105 for Italian).

You're then relying on an implicit conversion back to datetime to match the type of the target column.

The implicit conversion has a default style of 0 as you can see in the execution plan:

[Expr1003] = Scalar Operator(CONVERT_IMPLICIT(datetime,CONVERT(varchar(30),[@1],102),0))

(note: you should always specify the maximum length when using varchar)

When you use style 102 yyyy.mm.dd you must also set DATEFORMAT to YMD so SQL Server can parse the format under style 0.

When you use style 105 dd-mm-yyyy, you must set DATEFORMAT to DMY for the same reason.

The reason it works on one and not the other is the default DATEFORMAT for the language in each case.

See datetime and SET LANGUAGE.

share|improve this answer

edited 7 hours ago

answered 7 hours ago

Paul White♦Paul White

56.9k1414 gold badges298298 silver badges472472 bronze badges

add a comment | 

8

VALUES (convert(varchar,convert(datetime,D '2019-06-30'),102));

Starting from the inside, the ODBC escape sequence D '2019-06-30' returns a datetime.

You're then converting that to a string with 102 style (rather than 105 for Italian).

You're then relying on an implicit conversion back to datetime to match the type of the target column.

The implicit conversion has a default style of 0 as you can see in the execution plan:

[Expr1003] = Scalar Operator(CONVERT_IMPLICIT(datetime,CONVERT(varchar(30),[@1],102),0))

(note: you should always specify the maximum length when using varchar)

When you use style 102 yyyy.mm.dd you must also set DATEFORMAT to YMD so SQL Server can parse the format under style 0.

When you use style 105 dd-mm-yyyy, you must set DATEFORMAT to DMY for the same reason.

The reason it works on one and not the other is the default DATEFORMAT for the language in each case.

See datetime and SET LANGUAGE.

share|improve this answer

edited 7 hours ago

answered 7 hours ago

Paul White♦Paul White

56.9k1414 gold badges298298 silver badges472472 bronze badges

add a comment | 

VALUES (convert(varchar,convert(datetime,D '2019-06-30'),102));

Starting from the inside, the ODBC escape sequence D '2019-06-30' returns a datetime.

You're then converting that to a string with 102 style (rather than 105 for Italian).

You're then relying on an implicit conversion back to datetime to match the type of the target column.

The implicit conversion has a default style of 0 as you can see in the execution plan:

[Expr1003] = Scalar Operator(CONVERT_IMPLICIT(datetime,CONVERT(varchar(30),[@1],102),0))

(note: you should always specify the maximum length when using varchar)

When you use style 102 yyyy.mm.dd you must also set DATEFORMAT to YMD so SQL Server can parse the format under style 0.

When you use style 105 dd-mm-yyyy, you must set DATEFORMAT to DMY for the same reason.

The reason it works on one and not the other is the default DATEFORMAT for the language in each case.

See datetime and SET LANGUAGE.

share|improve this answer

edited 7 hours ago

answered 7 hours ago

Paul White♦Paul White

56.9k1414 gold badges298298 silver badges472472 bronze badges

VALUES (convert(varchar,convert(datetime,D '2019-06-30'),102));

[Expr1003] = Scalar Operator(CONVERT_IMPLICIT(datetime,CONVERT(varchar(30),[@1],102),0))

share|improve this answer

edited 7 hours ago

answered 7 hours ago

Paul White♦Paul White

56.9k1414 gold badges298298 silver badges472472 bronze badges

answered 7 hours ago

Paul White♦Paul White

56.9k1414 gold badges298298 silver badges472472 bronze badges

answered 7 hours ago

Paul White♦Paul White

56.9k1414 gold badges298298 silver badges472472 bronze badges