Oracle to_char question regarding Time [resolved]

[ahara]

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

[brucevde]

Sorry, I am confused. You indicate that specifying AM alone displays AM or PM correctly - so what is the problem?

select to_char(startDate, 'dd/mm/yyyy HH:MI:SS AM')

I cannot display the time in 24 hour time

Are you saying you don't know how to display the time in 24 hour format or its not allowed?

If the former

select to_char(startDate, 'dd/mm/yyyy HH24:MI:SS')

Note - minutes is MI not MM (month)

[ahara]

No, what I meant was that the insert uses AM alone, but I just tried the select with AM only and it worked - thank-you for your reply