Updating datetime2 column not working

  • 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.

  • This is working with SQL Server 2016.

    Things will work out.  Get back up, change some parameters and recode.

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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

  • 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.

  • There must be a trigger(s) on the table that is incorrectly handling a date string (?).

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Look closely at the help page I posted the link to.  Your string dates are wrong.

  • 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.

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher - Monday, May 7, 2018 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)

    Duh!

  • Lynn Pettis - Monday, May 7, 2018 4:01 PM

    ScottPletcher - Monday, May 7, 2018 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)

    Duh!

    Very helpful.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher - Monday, May 7, 2018 4:05 PM

    Lynn Pettis - Monday, May 7, 2018 4:01 PM

    ScottPletcher - Monday, May 7, 2018 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)

    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.

  • Lynn Pettis - Monday, May 7, 2018 5:06 PM

    ScottPletcher - Monday, May 7, 2018 4:05 PM

    Lynn Pettis - Monday, May 7, 2018 4:01 PM

    ScottPletcher - Monday, May 7, 2018 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)

    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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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.

  • ScottPletcher - Tuesday, May 8, 2018 6:58 AM

    Lynn Pettis - Monday, May 7, 2018 5:06 PM

    ScottPletcher - Monday, May 7, 2018 4:05 PM

    Lynn Pettis - Monday, May 7, 2018 4:01 PM

    ScottPletcher - Monday, May 7, 2018 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)

    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