Mdthbs

What could a Medieval society do with excess animal blood?

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

What do you call the motor that fuels the movement of a robotic arm?

How is /a/ pronounced before n/m in French?

Contributing to a candidate as a Foreign National US Resident?

what is the meaning of "stock" dilution on the Massive Dev Chart Website?

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

Who are the police in Hong Kong?

Construction of the word подтвержда́ть

Who pays for increased security measures on flights to the US?

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

Show that there are infinitely more problems than we will ever be able to compute

Can I deep fry food in butter instead of vegetable oil?

Solving Equations with HeavisideTheta Functions

How might boat designs change in order to allow them to be pulled by dragons?

Fine-tuning parameters for existing methods

What instances can be solved today by modern solvers (pure LP)?

Did Snape really give Umbridge a fake Veritaserum potion that Harry later pretended to drink?

Why is 一日 used instead of 日 to ask how your day is?

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

gzip compress a local folder and extract it to remote server

Is it possible that Curiosity measured its own methane or failed doing the spectrometry?

Is it possible to obtain the address of the 'this' pointer?

What is the right way to query an I2C device from an interrupt service routine?

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 8 hours ago

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

asked 9 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
  9 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
  9 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

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

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  mssqltips.com/sqlservertip/5206/…
  
  – Aaron Bertrand♦
  6 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 8 hours ago

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

asked 9 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
  9 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
  9 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

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

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  mssqltips.com/sqlservertip/5206/…
  
  – Aaron Bertrand♦
  6 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 8 hours ago

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

asked 9 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 8 hours ago

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

asked 9 hours ago

sgargelsgargel

20011 silver badge1313 bronze badges

share|improve this question

edited 8 hours ago

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

asked 9 hours ago

sgargelsgargel

20011 silver badge1313 bronze badges

edited 8 hours ago

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

edited 8 hours ago

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

asked 9 hours ago

sgargelsgargel

20011 silver badge1313 bronze badges

asked 9 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 8 hours ago

answered 8 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 8 hours ago

answered 8 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 8 hours ago

answered 8 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 8 hours ago

answered 8 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 8 hours ago

answered 8 hours ago

Paul White♦Paul White

56.9k1414 gold badges298298 silver badges472472 bronze badges

answered 8 hours ago

Paul White♦Paul White

56.9k1414 gold badges298298 silver badges472472 bronze badges

answered 8 hours ago

Paul White♦Paul White

56.9k1414 gold badges298298 silver badges472472 bronze badges