CODEWITHSUNDEEP
sqloracle-databasedate-arithmetic
Pinchas K
Pinchas K

Reputation: 1571

Convert this SQL Server query to Oracle

I have the following in a SQL Server query which I have to convert to Oracle sp

DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(ss, -L_LAST_TIME, TR.TR_DATETIME))) AS TRDATE,

Essentially you subtract L_LAST_TIME seconds from TR_DATETIME and then truncate the time part and keep only the date part.

Upvotes: 0

Views: 243

Answers (2)

A.B.Cade
A.B.Cade

Reputation: 16905

You can divide intervals: 

select trunc(TR.TR_DATETIME - interval L_LAST_TIME SECOND) AS TRDATE

or 

select trunc(TR.TR_DATETIME - NUMTODSINTERVAL(L_LAST_TIME, 'SECOND')) AS TRDATE

Upvotes: 1

APC
APC

Reputation: 146229

We can do arithmetic with dates in Oracle. 

select trunc(tr.tr_time - (l_last_time/86400)) as trdate
from tr
/

Dividing l_last_time by 86400 turns a number of seconds into a fraction of day. Subtracing it from the tr_time column gives you a new, earlier date. Truncating a date removes the time component.

Upvotes: 0

Related Questions