By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,355 Members | 1,470 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,355 IT Pros & Developers. It's quick & easy.

to_timestamp fomatting of Dates

rsrinivasan
100+
P: 221
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...
May 8 '07 #1
Share this Question
Share on Google+
12 Replies


frozenmist
Expert 100+
P: 179
Hi,
Try this
Expand|Select|Wrap|Line Numbers
  1.  
  2. 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
  3.  
This should solve it.
Cheers
May 8 '07 #2

rsrinivasan
100+
P: 221
Hi,
Try this
Expand|Select|Wrap|Line Numbers
  1.  
  2. 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
  3.  
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..
May 8 '07 #3

debasisdas
Expert 5K+
P: 8,127
replace this by previous query

Expand|Select|Wrap|Line Numbers
  1. 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
  2.  

only add HH24 in place of H24
May 8 '07 #4

rsrinivasan
100+
P: 221
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..
May 8 '07 #5

frozenmist
Expert 100+
P: 179
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
May 8 '07 #6

rsrinivasan
100+
P: 221
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...
May 8 '07 #7

frozenmist
Expert 100+
P: 179
Hi,
I think if you have the concatenated field as YYYY/MM/DD HH12:MI AM/PM
It would sort correctly.
Cheers
May 8 '07 #8

rsrinivasan
100+
P: 221
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....
May 8 '07 #9

frozenmist
Expert 100+
P: 179
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
May 9 '07 #10

frozenmist
Expert 100+
P: 179
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)
Expand|Select|Wrap|Line Numbers
  1. select  dt from table
  2. 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
  3.  
first substring selects year,the second month,third day, fourth am/pm and fifth for time

Cheers
May 9 '07 #11

rsrinivasan
100+
P: 221
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...
May 9 '07 #12

P: 1
[quote=rsrinivasan;2552332]Hi,
Thanks for ur reply..
what string function you have used at the end ....???
Mar 4 '14 #13

Post your reply

Sign in to post your reply or Sign up for a free account.