subtract int value to datetime


How can I substract int value (1 or 2 hours) to datetime?
I use
 and fs.DATETIME = (DATEDIFF(hh,cast(fs.DATETIME as time), CONVERT(date, fs.NBHOUR , 112)))

But the following error is returned :
Explicit conversion from data type int to date is not allowed.


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:

If you need to reduce hour from date you just simple write this


Open in new window

hope this will guide you how you can subtract hour from datetime
Vitor MontalvãoMSSQL Senior EngineerCommented:
You have a column called DATETIME?
And what data type is  fs.NBHOUR?
Can you provide sample data?
Olaf DoschkeSoftware DeveloperCommented:

The result type of DATEDIFF ( datepart , startdate , enddate )  is a difference (int) on some scale (ie hours). It needs a datepart and two datetimes as parameters.

The result type of DATEADD (datepart , number , date ) is a datetime, it adds a numeric value in some scale (again ie hours) to a given datetime. It needs a datepart, the number to add (negative to subtract of course) and a datetime as parameters.

What you want to compute seems to be

Open in new window

This assumes:
fs.DATETIME field is a datetime and you want to subtract the hours from it.
fs.NBHOUR is an int, the number of hours you want to subtract.

Bye, Olaf.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

bibi92Author Commented:
Yes, exactly,

 fs.DATETIME field is a datetime and you want to subtract the hours from it.
 fs.NBHOUR is an int, the number of hours you want to subtract.

I will test it this evening.

Best Regards
Olaf DoschkeSoftware DeveloperCommented:
It seems your think of DATEDIFF to subtract a given number as opposite to DATEADD, and the error messages you got on DATEDIFF led you to converting the parameters. That's finally led to computing an int and wanting to store it into a datetime, which gives you the error you have with your expression.

UPDATE fs Set DATETIME = DATEADD(hh, -fs.NBHOUR, fs.DATETIME) WHERE somecondition 

Open in new window

will be your solution.

Check first by just viewing the result via
SELECT DATETIME, NBHOUR, DATEADD(hh, -fs.NBHOUR, fs.DATETIME) as NewDatetime FROM fs WHERE somecondition 

Open in new window

Besides that, it's bad practice to use reserved words as column names, but that's something not fixed as easy and fast as it can be seen.

Bye, Olaf.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
absolutely, use DATEADD() for both add and subtract

DATEADD() returns a datetime value


DATEDIFF() returns an integer that represents the number of time units (e.g. days, hours etc) between 2 date/time values. This function does not add or subtract at all.
bibi92Author Commented:
Thanks a lot regards
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.