Updating datetime2 column not working

Question

Updating datetime2 column not working

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100948 More actions · Answer 1

Lynn Pettis - Tuesday, May 8, 2018 8:21 AM
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.

You're determine not to be directly helpful on things such as this, yeah, I got it.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

ScottPletcher - Tuesday, May 8, 2018 9:51 AM
Lynn Pettis - Tuesday, May 8, 2018 8:21 AM
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.
You're determine not to be directly helpful on things such as this, yeah, I got it.

Nope, I want people to do some research and if they still have a problem come back and ask for clarification. Sorry, but that was the way I was taught in the pre-internet world. It is a lost skill today that really needs to be encouraged. I have no problem helping anyone and I have given people just the answer to the question and seen them come back asking just about the same question time and time again (not saying the OP is one of them). The people who really want the help will also help themselves when given the push in the right direction. That starts by showing them where to find the answers to the their question.
Next thing you know those are the ones that start helping others as well.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

WebTechie - Monday, May 7, 2018 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.

Use colons, not periods, in the time parts.

ScottPletcher - Tuesday, May 8, 2018 9:51 AM
Lynn Pettis - Tuesday, May 8, 2018 8:21 AM
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.
You're determine not to be directly helpful on things such as this, yeah, I got it.

He's trying to get people to be more self sufficient. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 4

WebTechie - Monday, May 7, 2018 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.

Have you tried the ANSI/ISO Standard temporal display format which uses colons, not periods in the TIME field of a timestamp. "YYYY-MM-DD HH:MM:SS"

Please post DDL and follow ANSI/ISO standards when asking for help.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

jcelko212 32090 - Thursday, May 10, 2018 8:33 AM
WebTechie - Monday, May 7, 2018 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.
Have you tried the ANSI/ISO Standard temporal display format which uses colons, not periods in the TIME field of a timestamp. "YYYY-MM-DD HH:MM:SS"

Little late to the party Mr. Celko.