469,270 Members | 1,314 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

to_timestamp fomatting of Dates

rsrinivasan
221 100+
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
12 36464
frozenmist
179 Expert 100+
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
221 100+
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
8,127 Expert 4TB
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
221 100+
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
179 Expert 100+
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
221 100+
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
179 Expert 100+
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
221 100+
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
179 Expert 100+
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
179 Expert 100+
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
221 100+
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
[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.

Similar topics

8 posts views Thread by Riley | last post: by
7 posts views Thread by Alistair | last post: by
5 posts views Thread by PW | last post: by
10 posts views Thread by Colin Steadman | last post: by
7 posts views Thread by Bambero | last post: by
1 post views Thread by pitfour.ferguson | last post: by
2 posts views Thread by Jim Carlock | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.