DateTime Function
-
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!!!!!!
-
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
-
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 -
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
-
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)))
-
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.
-
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply