SQL Query for format of time in report

Question

Summary

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

Antoine KAIBER · Answer

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 :

timdexter · Answer

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