Oracle SQL – Date Difference In Days



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.



select s.row_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”

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: Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: