asked on

Argument data type datetime is invalid for argument 2 of dateadd function in stored procedure

I simply wish to add 1 day to a datetime value - its ok if i lose the time component.

UPDATE usertempx SET licenseExpirationDate = DateAdd(dd, #datelock.cmi_ended_report,1) From #datelock INNER JOIN
usertempx ON usertempx.licdetail_guid = #datelock.licdetail_guid;

I have tried a few permuations but no luck yet

adwiseman

#datelock.cmi_ended_report must be datatype of dateime, or smalldatetime

UPDATE usertempx SET licenseExpirationDate = #datelock.cmi_ended_report + 1
From #datelock
INNER JOIN usertempx ON usertempx.licdetail_guid = #datelock.licdetail_guid;

ASKER CERTIFIED SOLUTION

adwiseman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nphoenix

ASKER

DateAdd(dd, 1, #datelock.cmi_ended_report)

adwiseman

Ahh, you had your parameters backwards, didn't even see that.

Please post a question in community support to have your points refunded.

Also, please clean up some of your old questions that are still open.
4 open questions:
10/31/05 https://www.experts-exchange.com/questions/21614383/Argument-data-type-datetime-is-invalid-for-argument-2-of-dateadd-function-in-stored-procedure.html "Argument data type datetime is invalid f..."
10/20/05 https://www.experts-exchange.com/questions/21601901/debug-1723-for-vpn-like-tracert.html "debug 1723 for vpn like tracert"
09/28/05 https://www.experts-exchange.com/questions/21577803/mirror-2-IDE-Drives.html "mirror 2 IDE Drives"
07/26/05 https://www.experts-exchange.com/questions/21504743/Document-security-which-address-distribution-pdf-encrypted.html "Document security which address distribu..."

David Todd

Hi,

Do you want to loose the time component?

For instance, the last select gives tomorrow with no time ...

select getdate()

select dateadd( d, datediff( d, 0, getdate() ), 0 )

select dateadd( d, datediff( d, 0, getdate() ), 1 )

Replace getdate() with #datelock.cmi_ended_report

(This relies on the inheirent conversion between floats and datetime and automatically treating 0 and 1 as datetimes. Its way faster than converting from chars.)

Regards
David