updating a datetime column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

updating a datetime column

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-09-12 : 15:02:53

Need help to write an update sql query for a targetdate(datetime) column which is dependant on receiveddt(datetime),approveddt(datetim),TAT(int)

condition1if the receiveddt > approveddt then targetdate should be equal to receiveddate + TAT(number of days) excluding the weekend

condition 2
if if the receiveddt <+ approveddt then targetdate should be equal to approveddt + TAT(number of days) excluding the weekend

memorykills
Starting Member

18 Posts

Posted - 2011-09-12 : 16:31:17

maybe this one will work (if receiveddt and approveddt are only in weekdays)

UPDATE myTable
SET targetDate = DATEADD(DAY, (CASE (DATEPART(dw, receiveddt) + TAT % 5 ) % 7 WHEN 0 THEN 2 WHEN 6 THEN 1 ELSE 0 END), dateadd(day, TAT % 5, dateadd(week, TAT / 5, receiveddt)))
WHERE receiveddt > approveddt

UPDATE myTable
SET targetDate = DATEADD(DAY, (CASE (DATEPART(dw, approveddt) + TAT % 5 ) % 7 WHEN 0 THEN 2 WHEN 6 THEN 1 ELSE 0 END), dateadd(day, TAT % 5, dateadd(week, TAT / 5, approveddt)))
WHERE receiveddt < approveddt

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-09-13 : 08:20:58

Thank You.It worked.All the receiveddt and approveddt are only weekdays

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources