CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
Raj
Raj

Reputation: 10853

DateTime calculations in SQL Server 2008 R2

I have 3 columns in a table in my database - Created, Expires and Expiration_Interval. Both Created and Expires are datetimeoffset(0) and Expiration_Interval is of data type Time(0).

Created gets populated with SYSUTCDATETIME(), when inserting a new row. Data for Expiration_Interval is provided in the input XML in hh:mm:ss format, extracted and populated. The requirement is to populate Expires column with Created+Expiration_Interval. Here is my approach:

  INSERT INTO SESSIONS
  SELECT 
    Expiration_Interval,
    SYSUTCDATETIME(),
    DATEADD(s,((DATEPART(hh,Timeout)*3600)+(DATEPART(mi,Timeout)*60)+    DATEPART(ss,Timeout)),SYSUTCDATETIME())
  FROM input_xml -- CTE with shredded XML data

Is there an easier way to do this? This is on SQL Server 2008 R2

Upvotes: 3

Views: 658

Answers (3)

Andriy M
Andriy M

Reputation: 77687

I suggest you store the interval as an integer (seconds). You'd then only need to do the following:

DATEADD(s, Expiration_Interval, Created)

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You can cast Timeout to datetime and use + to add time to SYSUTCDATETIME().

SYSUTCDATETIME()+cast(Timeout as datetime)

Upvotes: 2

HABO
HABO

Reputation: 15816

Hate to say:

declare @Time as Time = '01:00:00.00'
declare @NoTime as Time = '00:00:00.00'
declare @Now as DateTimeOffset = SysDateTimeOffset()

select @Now as 'Now', @Time as 'One Hour',
    DateDiff( s, @NoTime, @Time ) as 'One Hour in Seconds',
    DateAdd( s, DateDiff( s, @NoTime, @Time ), @Now ) as 'One Hour Hence'

Tip: I generally capture "now" in a variable and then use it throughout a query or stored procedure. It avoids multiple function calls and keeps things synchronized. Unless, of course, it is an unusually long running wad of code and you really want to watch the progress.

Upvotes: 1

Related Questions