Oracle SQL – Date Difference In Days

 

Scenario

It was required to take a full extract of Service Requests from database. But there was a calculated field “Days Open” as follows:

IIf ([Status] = LookupValue(“SR_STATUS”, “Open”), Timestamp() – [Created],[Updated] – [Created])

Now the target was to design an Oracle SQL for calculating number of days a Service Request was open.

 

Solution

select s.row_id
, s.SR_STAT_ID
, To_char(s.created, ‘MM/DD/YYYY HH24:MI:SS’) “Created”
, To_char(s.last_upd, ‘MM/DD/YYYY HH24:MI:SS’) “Last_Updated”
, To_char(sysdate, ‘MM/DD/YYYY HH24:MI:SS’) “Today”
, Case
      When s.SR_STAT_ID = ‘Open’ Then to_char (sysdate – s.created, ‘999999D99’)
      Else to_char (s.last_upd – s.created, ‘999999D99’)
   End “Days Open”
from SIEBEL.S_SRV_REQ s;

Download SQL – Date Difference

Please note that I have utilized To_Char function with an explicit format specification.

 

Appreciate your feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: