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

Posted on 2011-10-05
Last Modified: 2012-05-12
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.
Question by:samic400
    LVL 4

    Expert Comment

    since it is logically datetime value, you need to substract 2 of what? minutes, seconds, days, ticks?
    LVL 59

    Accepted Solution

    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)
    LVL 13

    Author Comment

    I need to subtract 2 from days only from that float field.
    LVL 13

    Author Closing Comment

    This code is exactly what I needed. Thank you.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    This video discusses moving either the default database or any database to a new volume.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now