Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2011-10-05
Medium Priority
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
  • 2

Expert Comment

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

Accepted Solution

Kevin Cross earned 2000 total points
ID: 36919822
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

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

Author Closing Comment

ID: 36920395
This code is exactly what I needed. Thank you.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

564 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