Jump to content
Patrick H

Creating View on CHG with converted Dates

Recommended Posts

Hello everyone,

I want to create a view over the application SQL developer.

Unfortunately the date fields like Open_Date or Sched_Start_Date provide only the integer value of the date.

I am currently trying to convert it by an Oracle SQL statement like....

Select...
to_char('"CHG"."OPEN_DATE"','ddmmyyy'),....
from.....

But it is not working. Tried several alternatives and versions of this convertion but it is not working-.-

Could anyone help me?

Please find the query below...

  SELECT 
"CHG"."CHG_REF_NUM" as CHGRefNum,
"CHG"."OPEN_DATE",
"CHG"."TEMPLATE_NAME",
"USP_CHANGE_TYPE"."SYM" as CHGType,
"Release"."CHG_REF_NUM" as ReleaseRefNum,
"CHGSTAT"."SYM" as CHGStat,
"CA_CONTACT"."LAST_NAME",
"CA_CONTACT"."FIRST_NAME",
"CA_CONTACT"."USERID",
"CHG"."SUMMARY",
"CHG"."SCHED_START_DATE",
"CHG"."SCHED_END_DATE",
"CA_OWNED_RESOURCE"."RESOURCE_NAME" as BusinessService,
"CHG"."ID",
"Affected_Configuration_Item"."RESOURCE_NAME" as AffectedCI,
"CHG"."DESCRIPTION"
FROM   (
    (
        (
            (
                (
                    (
                    "MDBADMIN"."USP_LREL_ASSET_CHGNR" "USP_LREL_ASSET_CHGNR"
                    RIGHT OUTER JOIN
                    "MDBADMIN"."CHG" "CHG" ON "USP_LREL_ASSET_CHGNR"."CHG"="CHG"."ID")
                    FULL OUTER JOIN "MDBADMIN"."CA_OWNED_RESOURCE" "Affected_Configuration_Item"
                    ON "USP_LREL_ASSET_CHGNR"."NR"="Affected_Configuration_Item"."OWN_RESOURCE_UUID")
                    LEFT OUTER JOIN "MDBADMIN"."USP_CHANGE_TYPE" "USP_CHANGE_TYPE"
                    ON "CHG"."CHGTYPE"="USP_CHANGE_TYPE"."ID"
                    ) LEFT OUTER JOIN "MDBADMIN"."CA_OWNED_RESOURCE" "CA_OWNED_RESOURCE"
                    ON "CHG"."PROJECT"="CA_OWNED_RESOURCE"."OWN_RESOURCE_UUID"
                    )LEFT OUTER JOIN "MDBADMIN"."CHG" "Release" ON "CHG"."PARENT"="Release"."ID"
                    ) LEFT OUTER JOIN "MDBADMIN"."CHGSTAT" "CHGSTAT" ON "CHG"."STATUS"="CHGSTAT"."CODE"
                    ) LEFT OUTER JOIN "MDBADMIN"."CA_CONTACT" "CA_CONTACT" ON "CHG"."ASSIGNEE"="CA_CONTACT"."CONTACT_UUID"
WHERE  "CHG"."OPEN_DATE">1408370000 and "CHG"."TEMPLATE_NAME" is null
ORDER BY "CHG"."CHG_REF_NUM";

Share this post


Link to post
Share on other sites

In Service Desk dates are stored as number of seconds since epoch (1970-01-01 00:00 GMT). To get the date use the MSSQL Dateadd function

For example:

Dateadd(ss, chg.sched_start_date - 14400, '1970/01/01') // the 14400 is a 4 hour offset from GMT in seconds

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...