Oracle Analytics Cloud and Server

Products Banner

SQL Query for format of time in report

Received Response
42
Views
2
Comments

Summary

SQL Query for format of time in report

Content

Hello,

I'm working on a report where I have a field which correspond to the time of arrival of a truck at a shipment stop. I would like the time displayed (which is currently set as local time) to be in french format if local shipment stop is in France. Currently I have this SQL query :

, to_char(utc.get_local_date(itm.STOP_PLANNED_ARRIVAL, itm.location_gid), 'DD-MM-YYYY') ||' '|| to_char(utc.get_local_date(itm.STOP_PLANNED_ARRIVAL, itm.location_gid), 'HH:MI AM') STOP_PLANNED_DATE_ARRIVAL

, to_char(utc.get_local_date(itm.STOP_PLANNED_DEPARTURE, itm.location_gid), 'DD-MM-YYYY') ||' '|| to_char(utc.get_local_date(itm.STOP_PLANNED_DEPARTURE, itm.location_gid), 'HH:MI AM') STOP_PLANNED_DATE_DEPARTURE

from SHIP_STOP_ITEM_BOV itm

So the time is displayed in the AM format, without taking account of the location of country. How would I do that ?

Thanks,


Antoine

Answers

  • timdexter
    timdexter ✭✭✭✭✭

    Couple of q's

    1. Is there a reason you can not combine the date and time formatting, rather than two separate calls to get_local_date

    2. For the French time format are you looking for say '12h00' for noon? If not, what format are you wanting?

    3. What values are in the itm.location_gid column?

    4. What is the format of the date returned by the get_local_date?

    Tim

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    1. No particular reason. I had two separate fields before thats why I kept it this way but I changed that to shorten it :

    , to_char(utc.get_local_date(itm.STOP_PLANNED_ARRIVAL, itm.location_gid), 'DD-MM-YYYY HH:MI AM') STOP_PLANNED_DATE_ARRIVAL

    , to_char(utc.get_local_date(itm.STOP_PLANNED_DEPARTURE, itm.location_gid), 'DD-MM-YYYY HH:MI AM')

    STOP_PLANNED_DATE_DEPARTURE

    2. Yes this is the french format. For midnight you would be writing 00h00, for 7PM -> 19h...

    3. The values in itm.location_gid are just ID's of location (plants). These ID's must refer to a country etc so the get_local_date enables it to define what time zone to use.

    4. get_local_date enables me to have a date with local time with this format :