subtract int value to datetime

Hello,

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.

Thanks

Regards
bibi92Asked:
Who is Participating?
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 GargBusiness Intelligence DeveloperCommented:
Hello,


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

SELECT GETDATE(),DATEADD(HOUR,-2,GETDATE())

Open in new window


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

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
DATEADD(hh, -fs.NBHOUR,fs.DATETIME)

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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
0
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.
1

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
PortletPaulfreelancerCommented:
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.
0
bibi92Author Commented:
Thanks a lot regards
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.