DateTime Function

Dear all,

I am writing SQL statement to manipulate datetime value.

Say to add 1 day, 1 minute & 1 hour to datetime '2004-03-10 14:00:00.000'.

I use the following SQL statement;

fac_date_cnt -> integer value 1
book_date -> datetime value '2004-03-10 14:00:00.000'.

UPDATE SCH_SET
 SET fac_send_date = DateAdd(day,fac_date_cnt, f.book_date) + DateAdd(hour,fac_hour, f.book_date) + DateAdd(minute,fac_minute, f.book_date)
 FROM SCH_SET t, FAC_BOOK f
 WHERE upper(set_id) = 'A000001' and upper(pcode) = 'A';

And then the result is very strange:
'2108-06-20 04:00:00.000'

What wrong with this statement?? Should I not use '+' between DateAdd()?? And then what should I do?

Thanks you for suggestion!!!Try:

DATEADD(minute,fac_minute,DATEADD(hour,fac_hour,DATEADD(day, fac_date_cnt, f.book_date)))|||Thanks you for reply!!!

It works well.

I just want to ask why I need to embed all DATEADD() into one??

Thanks!!!|||With your original query, you were actually adding three different dates together, after performing a DATEADD on each date. What you want to do instead is perform three distinct DATEADD operations on the same date.

An even easier way to perform this operation is to simply transalte everything into the least common denominator and then add that. For example:

DATEADD(minute, 1 + (25 * 60), @.originalDate)