to_timestamp fomatting of Dates

[rsrinivasan]

I run this code

select to_timestamp('3 1-12-2006 23:34:59','DD-MM-YYYY HH24:MI:SS') from dual;

But it Displays like this

31-DEC-06 11.34.59.000000 000 PM

But i expect this format
"MM/DD/YYYY HH:MI AM"

The separator should be /(slash) instead of -(hyphen).
The Second shoould not be displayed.

plz help me...

[frozenmist]

Hi,
Try this

 
select to_char(to_timestamp('31-12-2006 23:34:59','DD-MM-YYYY   H24:MI:SS'),'MM/DD/YYYY HH12:MI AM') from dual
 

This should solve it.
Cheers

[rsrinivasan]

Hi,
Try this

 
select to_char(to_timestamp('31-12-2006 23:34:59','DD-MM-YYYY   H24:MI:SS'),'MM/DD/YYYY HH12:MI AM') from dual
 

This should solve it.
Cheers

Fine... This is worked as i expected, but the field should not be converted to string. Because further i have to sort that field. If it is converted to string i can't sort that field. Because it consists AM and PM field.

plz reply what i need..

[debasisdas]

replace this by previous query

select to_char(to_timestamp('31-12-2006 23:34:59','DD-MM-YYYY   HH24:MI:SS'),'MM/DD/YYYY HH12:MI AM') from dual
 

only add HH24 in place of H24

[rsrinivasan]

Ok Fine... This is worked as i expected, but the field should not be converted to string. Because further i have to sort that field. If it is converted to string i can't sort that field. Because it consists AM and PM field.

plz reply what i need..

[frozenmist]

Hi,
You can store the value of date in the default Oracle format itself. So it would be possible for sorting. You can use the to_char function to change the format of the date to what you want when ever you are displaying.
Would this solve your problem?

Cheers

[rsrinivasan]

Thanks for ur reply...
My table oracle table format is

order_entry_dat e ---- date
order_entry_tim e ---- varchar2(10)

My client expects to concat these two fields and then sort that concatinated field. If i concat these two fields it is changed as string. then When i sort that field, it does not bother about AM and PM. It only sorts Date and Time.. it did not bother AM and PM..

thanks...

[frozenmist]

Hi,
I think if you have the concatenated field as YYYY/MM/DD HH12:MI AM/PM
It would sort correctly.
Cheers

[rsrinivasan]

Hi,
No. If the concatenated field is String, it is not sorted correctly. Only sorted, when it is timestamp field.

If it is String, it looks like this after sorting,

01-JAN-07 01.00 AM
01-JAN-07 02.00 PM
01-JAN-07 03.00 AM
01-JAN-07 04.00 AM


But It should be

01-JAN-07 01.00 AM
01-JAN-07 03.00 AM
01-JAN-07 04.00 AM
01-JAN-07 02.00 PM

See Am and Pm closely....

[frozenmist]

Hi,
If you store the concatenated field as YYYY/MM/DD HH12:MI AM/PM
Then you can sort using
(assume concatenated field is dt)
Select dt from table order by substr(dt,1,10) ,substr(dt,leng th(dt)-2,length(dt)),s ubstr(dt,11,5)

The first substring is for date, second for am/pm and third for time.
Would this help?
Cheers