Oracle to_char question regarding Time [resolved]
Hello;
I have an Oracle table that stores scheduling information. The problem I am having is the fields that store the date and time of the scheduled slot are inserted like so:
to_date('04/16/2004 8:15 PM', 'mm/dd/yyyy HH:MI:SS AM')
The person who originally wrote this code populated an array with string values like:
8:00 AM
8:15 AM
8:30 AM
....
10:00 PM
10:15 PM
etc
So the logic used when inserting involves pulling one of these 15 minute slot string values from the array and it gets put into the insert statement after the date - and surprisingly this does work (even if 'AM' is specified in the format string)....the problem I have is this:
if I select the date like:
select startDate from.....
I get the date and the time with AM or PM
But my app is now multi-lingual, so if the client is French, or Spanish, I need to display a different date format (dd/mm/yyyy)...so when I do:
select to_char(startDate, 'dd/mm/yyyy HH:MM:SS')
I lose the AM or PM part...and the kicker is - I cannot display the time in 24 hour time - I am compelled to change the date format but leave the same time format. Is there a way I can do something like:
select to_char(startDate, 'dd/mm/yyyy HH:MM:SS AM/PM') and get the right suffix (AM or PM)?
I tried the above and it returns:
16/04/2004 8:15 AM/PM - it gives me a literal
I have checked our Oracle bible and found nothing. I am hoping to solve this with SQL as opposed to using VB (if its possible)
Thanks
