May 7, 2018 at 3:19 pm
Hello,
Trying to update some datetime2 columns.
UPDATE T_MYTABLE SET dateTransferred = '2018-03-26 05.00.00' where id = 1223
Causes an error. So, I tried casting it.
UPDATE T_MYTABLE SET dateTransferred = Cast('2018-03-26 05.00.00' as datetime2) where id = 1223
Got another error. Trying using convert.
UPDATE T_MYTABLE SET dateTransferred = Convert(Datetime2,'2018-03-26 05.00.00',103) where id = 1223
Got an error.
Error message:
Conversion failed when converting date and/or time from character string.
I must be missing something and I need another set of eyes. Can anyone see where my syntax is wrong?
Thanks.
Things will work out. Get back up, change some parameters and recode.
May 7, 2018 at 3:21 pm
This is working with SQL Server 2016.
Things will work out. Get back up, change some parameters and recode.
May 7, 2018 at 3:35 pm
Get rid of the dashes in the date, they can cause issues.
UPDATE T_MYTABLE SET dateTransferred = '20180326 05.00.00' where id = 1223
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 7, 2018 at 3:36 pm
Looks like the date strings are wrong. Take a close look at this: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
May 7, 2018 at 3:46 pm
Removing the dashes didn't seem to resolve it. Got the same error message.
Things will work out. Get back up, change some parameters and recode.
May 7, 2018 at 3:49 pm
There must be a trigger(s) on the table that is incorrectly handling a date string (?).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 7, 2018 at 3:54 pm
Look closely at the help page I posted the link to. Your string dates are wrong.
May 7, 2018 at 3:55 pm
I do appreciate the thoughts and ideas. But I checked and no triggers. Can't remember when I was stomped by an update statement.🙂
Things will work out. Get back up, change some parameters and recode.
May 7, 2018 at 3:58 pm
Yeah, you must use colons instead of dots in the time. I'm used to seeing either one ... but SQL isn't.
SELECT CAST('20180326 05:00:00' as datetime2)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 7, 2018 at 4:01 pm
ScottPletcher - Monday, May 7, 2018 3:58 PMYeah, you must use colons instead of dots in the time. I'm used to seeing either one ... but SQL isn't.SELECT CAST('20180326 05:00:00' as datetime2)
Duh!
May 7, 2018 at 4:05 pm
Lynn Pettis - Monday, May 7, 2018 4:01 PMScottPletcher - Monday, May 7, 2018 3:58 PMYeah, you must use colons instead of dots in the time. I'm used to seeing either one ... but SQL isn't.SELECT CAST('20180326 05:00:00' as datetime2)
Duh!
Very helpful.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 7, 2018 at 5:06 pm
ScottPletcher - Monday, May 7, 2018 4:05 PMLynn Pettis - Monday, May 7, 2018 4:01 PMScottPletcher - Monday, May 7, 2018 3:58 PMYeah, you must use colons instead of dots in the time. I'm used to seeing either one ... but SQL isn't.SELECT CAST('20180326 05:00:00' as datetime2)
Duh!
Very helpful.
Sorry, but I was. I posted a link to the documentation for CAST/CONVERT and told the OP to read carefully. Highlighting the CONVERT or CAST function name in SSMS and pressing F1 is your friend.
May 8, 2018 at 6:58 am
Lynn Pettis - Monday, May 7, 2018 5:06 PMScottPletcher - Monday, May 7, 2018 4:05 PMLynn Pettis - Monday, May 7, 2018 4:01 PMScottPletcher - Monday, May 7, 2018 3:58 PMYeah, you must use colons instead of dots in the time. I'm used to seeing either one ... but SQL isn't.SELECT CAST('20180326 05:00:00' as datetime2)
Duh!
Very helpful.
Sorry, but I was. I posted a link to the documentation for CAST/CONVERT and told the OP to read carefully. Highlighting the CONVERT or CAST function name in SSMS and pressing F1 is your friend.
Vastly less helpful than just saying "use colons in the time", though, eh?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 8, 2018 at 7:40 am
Lynn, sorry to let you down. I did look at the url, but I definitely missed the colons.
Scott, Thank you, thank you and again thank you.
I was actually stuck on that all day searching the internet for why that was not working. Talk about a learning moment. I won't soon forget that one.
Thanks everyone!
Things will work out. Get back up, change some parameters and recode.
May 8, 2018 at 8:21 am
ScottPletcher - Tuesday, May 8, 2018 6:58 AMLynn Pettis - Monday, May 7, 2018 5:06 PMScottPletcher - Monday, May 7, 2018 4:05 PMLynn Pettis - Monday, May 7, 2018 4:01 PMScottPletcher - Monday, May 7, 2018 3:58 PMYeah, you must use colons instead of dots in the time. I'm used to seeing either one ... but SQL isn't.SELECT CAST('20180326 05:00:00' as datetime2)
Duh!
Very helpful.
Sorry, but I was. I posted a link to the documentation for CAST/CONVERT and told the OP to read carefully. Highlighting the CONVERT or CAST function name in SSMS and pressing F1 is your friend.
Vastly less helpful than just saying "use colons in the time", though, eh?
Obviously you don't believe people should be able to read documentation, that is why it is out there and so easily accessible from SSMS.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply