Mfcttrf

Olive oil in Japanese cooking

Can you move between the attacks of a Twinned Booming Blade

Does this circuit have marginal voltage level problem?

Does this house rule, for determining hit point maximum, make characters weaker than RAW?

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

Recolour existing plots

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

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

How to widen the page

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

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

What verb goes with "coup"?

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

Should I warn my boss I might take sick leave

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

What is the difference between a historical drama and a period drama?

Are semivowels pronounced differently than vowels?

Is there any way for a Half-Orc Sorcerer to get proficiency with a heavy weapon?

How to compute the number of centroids for K-means clustering algorithm given minimal distance?

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

Phrasing "it says" or "it reads"

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

Wrong Output in self defined Quaternionic Multiplication

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

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

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

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

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  @McNets Comments are not for guessing.
  
  – Paul White♦
  9 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 9 hours ago

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

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

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  @McNets Comments are not for guessing.
  
  – Paul White♦
  9 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 9 hours ago

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

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

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

asked 10 hours ago

sgargelsgargel

20011 silver badge1313 bronze badges

share|improve this question

edited 9 hours ago

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

asked 10 hours ago

sgargelsgargel

20011 silver badge1313 bronze badges

edited 9 hours ago

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

edited 9 hours ago

Paul White♦

56.9k1414 gold badges298298 silver badges472472 bronze badges

asked 10 hours ago

sgargelsgargel

20011 silver badge1313 bronze badges

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

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

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

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

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

answered 9 hours ago

Paul White♦Paul White

56.9k1414 gold badges298298 silver badges472472 bronze badges

answered 9 hours ago

Paul White♦Paul White

56.9k1414 gold badges298298 silver badges472472 bronze badges

answered 9 hours ago

Paul White♦Paul White

56.9k1414 gold badges298298 silver badges472472 bronze badges