Post reply

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

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

  • March 21, 2011 at 3:45 pm

    #1301619

    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'

  • March 21, 2011 at 4:44 pm

    #1301637

    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)
    Intro to Tally Tables and Functions

  • March 26, 2011 at 4:33 pm

    #1304003

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

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

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