How to Subtract timespan from datetime

Question

Post reply

How to Subtract timespan from datetime

Ramesh-496278

SSC Veteran

Points: 287
More actions
January 4, 2008 at 2:40 am

#154043

Hi i have written one CLR Procedure as follows....
CLR procedure
--------------
SqlCommand comGetT = new SqlCommand("select getdate()", sqlCon, sqlTrans);
SqlDataReader ReaderGetT;
ReaderGetT = comGetT.ExecuteReader();
ReaderGetT.Read();
DateTime date1 = System.DateTime.Parse(ReaderGetT[0].ToString());
DateTime currDt;
if (timeElasped.Days > 0 || timeElasped.Hours > 0 || timeElasped.Minutes > 0)
{
currDt = date1.Subtract(timeElasped);
}
now i would like to convert CLR to T-SQL...
Please help me how to Convert the following statement in SQL server 2005
currDt = date1.Subtract(timeElasped);

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

Assuming that "TimeElapsed" is of the DateTime datatype...

SELECT currDt = GETDATE()-TimeElapsed

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ramesh-496278 SSC Veteran Points: 287 More actions · Answer 2

no its not a DateTime........

its a TimeSpan Datatype.....

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

The TimeSpan Structure from .NET doesn't serialize into anything compatible in SQL natively. You'd really want to override the ToString method, so that it will return a float in increments of days, or return each of the units separately.

If you're stuck with using the string it outputs by default, here's a sample way to convert TimeStamp strings to a datetime.

declare @tspan as varchar(20)

set @tspan= '11.15:45:52.15' -- a timespan string

select

dateadd(

day,

cast(

left(@tspan,patindex('%.%',@tspan)-1)

as int

),

cast(

substring(@tspan,

patindex('%.%',@tspan)+1,

100)

as datetime)

)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?