how to subtract 2 from from my datetime value in t-sql

I have a field in my table called capturedate which stores a float value which looks like something like this - 40821.28441

my t-sql code is like this:

declare @mypunchdate datetime;
set @mypunchdate = cast(capturedate as datetime);

this code converts the value to a datetime value fine - now what I need to do is before I pass @mypunchdate to my stored procedure I need to subtract 2 from it - so I need to add more code above so it takes that capturedate subtracts 2 and then I put it in my @mypunchdate variable.

What would the code look like to do this above?

Thanks so much in advance. I am lost when it comes to t-sql.
LVL 13
samic400Asked:
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.

vvkCommented:
since it is logically datetime value, you need to substract 2 of what? minutes, seconds, days, ticks?
0
Kevin CrossChief Technology OfficerCommented:
I agree. It matters what we are talking about. In numerical form, datetime value represents the number of days since date 0 (i.e., 1900-01-01 00:00:00.000) with the decimal part being the time stamp. Therefore, to subtract two days, you can do this before conversion by simply subtracting two from the float.

declare @capturedate float = 40821.28441, @offset float = 2.0;
declare @mypunchdate datetime;
set @mypunchdate = cast(@capturedate - @offset as datetime);
select @mypunchdate;

Open in new window


Otherwise, you will need to perform DATEADD() based on specific date part you want to affect as eluded to by vvk. e.g., DATEADD(minute, -2, @capturedate)
0

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
samic400Author Commented:
I need to subtract 2 from days only from that float field.
0
samic400Author Commented:
This code is exactly what I needed. Thank you.
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 2005

From novice to tech pro — start learning today.