CODEWITHSUNDEEP
sqlsql-serverdatetime
indofraiser
indofraiser

Reputation: 1024

Change value with SQL, datetime error

Aim: Update NextDue when Priority has a requested value. NextDue is updated with a date from CurrentDate plus x days.

Edit/Update: CurrentDate is an actual field and not todays date. I'll rename this going forwards.

Error:

Conversation failed when converting datetime from character string)

Code:

   Update TblDates
   SET  Priority =  CASE
                      WHEN Priority='A' then NextDue + (CurrentDate +30)
                      WHEN Priority='B' then NextDue + (CurrentDate +730)
                   END
   WHERE NextFRADue in ('A','B')

Field type:

Priority  = Text
NextFRADue = DateTime
CurrentDate = DateTime

Date stored as "YYYY-MM-DD"

Upvotes: 0

Views: 62

Answers (2)

Bernd Linde
Bernd Linde

Reputation: 2152

First off:
You are misinterpreting the syntax of an update statement.
With the way you are writing them, you are expecting to update NextDate with DATEADD() while it all has something to do with LastDate, on a case of Priority.

The basic syntax of the update statement (as per MSDN) 

UPDATE <TableName>
   SET <Column That Will Be Updated> = <Statement that determines the new value>,
       <Column That Will Be Updated> = <Statement that determines the new value>
 WHERE <Normal where clause>

Your comment of with code below therefore is incorrect.

What I need to do is change the field "NextDate" based on the field "LastDate".
NextDate will be a set amount of days forward from "LastDate" and this should be based on the value is a third field "Priority" 

    Update TBLTEST  
       SET LastDate = CASE WHEN Priority='1' THEN (NextDate+ DATEADD(Day, 30))  
                           WHEN Priority='2' THEN (NextDate+ DATEADD(Day, 30))  
                      END  
     WHERE NextDate in ('1','2')

Updated:
From this, the code that you are looking for would be something like this:

UPDATE TblDates
       -- Update the column NextDue
   SET NextDue = CASE
                   -- Add NextDate number of days to the LastDate DateTime value, when the Priority value is 1
                   WHEN Priority='1' then DATEADD(day, (30+NextDate), LastDate)
                   -- Add NextDate number of days to the LastDate DateTime value, when the Priority value is 2
                   WHEN Priority='2' then DATEADD(day, (30+NextDate), LastDate)
                 END
 WHERE NextFRADue in ('1','1')

Further reading:
MSDN: DATEADD

.
Old answer since not sure which version was the one that was right for your question
The code that you are looking for would be something like this:

Update TblDates
   SET NextDue = CASE
                   WHEN Priority='A' then DATEADD(day, 30, CurrentDate)
                   WHEN Priority='B' then DATEADD(day, 730, CurrentDate)
                 END
 WHERE NextFRADue in ('A','B')

Upvotes: 3

Alex
Alex

Reputation: 21766

There seems to be several mistakes in our code snippet. In SQL Server you can use DATEADD to add your days:

 UPDATE  TblDates
 SET         NextDue= CASE WHEN Priority = 'A'
                      THEN DATEADD(DAY, 70, CurrentDate)
                      WHEN Priority = 'B'
                      THEN DATEADD(DAY, 730, CurrentDate)
                  END
 WHERE   NextFRADue IN ( 'A', 'B' )

I have also changed the query to update NextDue. In your code example you are adding NextDue to DATEADD(DAY, <days>, CurrentDate) and updating priority with this value.

Upvotes: 1

Related Questions