DateTime Function

Question

Post reply

DateTime Function

nghoiming02

SSC-Addicted

Points: 478
More actions
March 9, 2004 at 7:51 am

#84528

Dear all,
Say to use DateAdd() function to add 1 day, 1 hour & 1 minute to the datetime '2004-03-10 14:00:00.000'.
The following are SQL statement :
fdate_cnt, fhour, fmin are integer value
book_date are the datetime value as above
UPDATE SCH_SET
    SET fac_send_date = DateAdd(day,fdate_cnt, f.book_date)
                   + DateAdd(hour,fhour, f.book_date))
                    + DateAdd(minute,fmin, f.book_date)
    FROM SCH_SET t, FAC_BOOK f
    WHERE upper(set_id) = 'A000001' and upper(pcode) = 'A';
The following output display very strange:
2108-06-20 04:00:00.000

I don't know what happen to DateAdd(). What error I have in this statement?? Pls help help me!!!!
Thanks you for reply!!!!!!

Viewing 7 posts - 1 through 6 (of 6 total)

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

fhanlon SSChampion Points: 13131 More actions · Answer 1

You cant add dates together and expect a reasonable response.

SET fac_send_date = DateAdd(day,fdate_cnt, f.book_date)

+ DateAdd(hour,fhour, f.book_date))

+ DateAdd(minute,fmin, f.book_date)

This statement really says add a day to some date, the add an hour to this date, then add a minute to this date. Now take the 3 dates and add then together. Taking the 3 new dates and adding then together will give you nothing of value. If you did this in 3 steps this would be ok.

as in

DECLARE @TempDate as Datetime

SELECT @TempDate = f.book_date

FROM SCH_SET t, FAC_BOOK f

WHERE upper(set_id) = 'A000001' and upper(pcode) = 'A';

SET @TempDate = DateAdd(day,1, f.book_date)

SET @TempDate = DateAdd(hour,1, @TempDate ) -- now add 1 hour

SET @TempDate = DateAdd(minute,1, @TempDate ) -- now add 1 minute

Francis

Jonathan SSC-Insane Points: 20427 More actions · Answer 2

UPDATE SCH_SET

SET fac_send_date = DateAdd(n, fdate_cnt*24*60 + fhour*60 + fmin, f.book_date)

FROM SCH_SET t, FAC_BOOK f

WHERE upper(set_id) = 'A000001' and upper(pcode) = 'A'

--Jonathan

fhanlon SSChampion Points: 13131 More actions · Answer 3

Why do it in 3 lines when it can be done in one? Nice one Johnathon. Convert the time to be added to the same thing (minutes) and add it all at once. Slick. Why didn;t I think of it.

Francis

nghoiming02 SSC-Addicted Points: 478 More actions · Answer 4

Thanks all!! I know how to do that!!

After my test, this is should be okay, I think:

DATEADD(minute,fac_minute,DATEADD(hour,fac_hour,DATEADD(day, fac_date_cnt, f.book_date)))

peterclark SSC Rookie Points: 48 More actions · Answer 5

Three lines are better than one when it makes it easier to read and understand.

When you have moved on and some else has to maintain your code they will thank you for readability not slickness! As an old hand at programming my vote goes for the three line version.

Jonathan SSC-Insane Points: 20427 More actions · Answer 6

A UDF will both perform worse and cause the maintenance programmer to deal with another object. I see nothing wrong with the readablity of the OP's solution, and that's the reason why I used "*24*60" rather than "*1440" in my code, of course.

I do suggest, though, making the predicates SARGable. If the columns are case sensitive (which would be unusual), then WHERE SetID IN ('A000001','a000001') AND PCode IN ('A','a') will perform better when the columns are indexed.

--Jonathan