Hello everybody. I am trying to insert some data in table Production.WorkOrder. There are some data which type is datetime. I am trying to insert that type of data with CONVERT keyword. But when I run my query I get this. What I need to do to solve this problem?
April 24, 2021 at 1:56 pm
Datetime has no format so using CONVERT with a format code is not necessary. The code should work if CONVERT is completely removed. Or you could use CAST instead.
select cast('2021-04-24 13:50:04.783 ' as datetime);
declare @dt datetime='2021-04-24 13:50:04.783 ';
select @dt;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
You don't need the CONVERT - you can just pass the datetime string and the value will be implicitly converted (assuming the columns data type is datetime and the string literal is valid).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 24, 2021 at 3:36 pm
datetime DOES have a format on convert - and that is what is causing it to fail.
format 1 is US - so date string should be on format "mm/dd/yyyy"
for YYYY-MM-DD with 24 hour time the formats should be 120 or 121
April 24, 2021 at 3:46 pm
datetime DOES have a format on convert - and that is what is causing it to fail.
format 1 is US - so date string should be on format "mm/dd/yyyy"
for YYYY-MM-DD with 24 hour time the formats should be 120 or 121
Since this is true the format 0 (but not 100) works as well
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
April 24, 2021 at 4:09 pm
Rereading what both Jeffery and me posted, it's correct and doesn't need editing. The CONVERT is not necessary. Frederico is also correct too. CONVERT will work if the correct format is provided. Conversely, it won't work if an incorrect one is provided. Either way this reply should get me to 1,400 SSC points which is a nice round number.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
April 24, 2021 at 4:13 pm
frederico_fonseca wrote:datetime DOES have a format on convert - and that is what is causing it to fail.
format 1 is US - so date string should be on format "mm/dd/yyyy"
for YYYY-MM-DD with 24 hour time the formats should be 120 or 121
Since this is true the format 0 (but not 100) works as well
yes and no - if no format is specified or if format 0 (zero) is used it is affected by "set dateformat and/or language " settings.
the 2 cases below - first one works as a specific format was specified while the second fails.
SET LANGUAGE Italian;
select convert(datetime, '2021-04-24 13:50:04.783 ' , 120);
SET LANGUAGE Italian;
select convert(datetime, '2021-04-24 13:50:04.783 ' , 0);
April 25, 2021 at 5:08 am
Thank you guys a lot , I fix the problem. You are the best 😀
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply