Inserting Datetime field into a update statement to update a nvarchar field

Question

Post reply

Inserting Datetime field into a update statement to update a nvarchar field

SQLTestUser

Hall of Fame

Points: 3997
More actions
March 21, 2011 at 3:22 pm

#236528

I have a field named earlyLate in table time which has to be updated with earlytime and latetime whihc are part of the same table but have a datetime dataype. so i tried this
Table name
"Time"
earlytime (datetime)
Latetime (datetime)
earlyLate nvarchar (max)
What i am trying to do:
update Time
where earlyLate = 'the earliest time this can be completed is' + earlytime + 'and the latetst time this can be completed is' + latetime. + 'Please follow the early late time and complete the task accordingly'
and got an error as
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
how can i incorporate the datetime fields ( earlytime and latetime columns into the earlyLate column) in this update statement and insert it into the nvarchar field.

Viewing 4 posts - 1 through 3 (of 3 total)

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

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 1

I simply had to add the statement CAST and convert the datatype into nvarchar and it did the trick

update Time

where earlyLate = 'the earliest time this can be completed is' + CAST (earlytime as nvarchar(4000)) + 'and the latetst time this can be completed is' + CAST (latetime as nvarchar(4000)) + 'Please follow the early late time and complete the task accordingly'

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

Good. As a side bar, though, I wouldn't store the calculated column as a VARCHAR(MAX) because the full message is only required at display time.

--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)

flylife2k SSC Journeyman Points: 81 More actions · Answer 3

did the same only a couple of days ago, CAST worked a doddle