to_timestamp fomatting of Dates

[rsrinivasan]

I run this code

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

But it Displays like this

31-DEC-06 11.34.59.000000000 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_date ---- date
order_entry_time ---- 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,length(dt)-2,length(dt)),substr(dt,11,5)

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

[frozenmist]

Hi,
If you still want it in MM/DD/YYYY HH12:MI AM/PM format then you can change the order by clause as
(assume dt is the concatenated field)

select  dt from table
order by substr(dt,7,4),substr(dt,1,2),substr(dt,4,5),substr(dt,length(dt)-1,2),substr(dt,11,7) asc
 

first substring selects year,the second month,third day, fourth am/pm and fifth for time

Cheers

[rsrinivasan]

Hi,
Thanks for ur reply..
your concept is nice...
but sometime i have null value in time column... so i cant use it..
Now it is worked as i expect. But i used someother way..

What i did is
1. I concated DATE and TIME Column
2. Then I converted into TIMESTAMP type.
3. Then I sorted that TIMESTAMP column
4. Then by using String function I display what I need.

bye...

[subramanir42]

[quote=rsrinivasan;2552332]Hi,
Thanks for ur reply..
what string function you have used at the end ....???