jQuery in Action, 2nd edition*
The moose likes Oracle/OAS and the fly likes so I have a sql date format I can't figure out Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "so I have a sql date format I can Watch "so I have a sql date format I can New topic
Author

so I have a sql date format I can't figure out

Lavanya Halliwell
Ranch Hand

Joined: Apr 09, 2010
Posts: 57
what is the format for this kind of date: 12/13/2010 17:14:23.220000 PM ?

I can't seem to find any chart with that on it?

Lavanya



ps - I can do this: MM/DD/YYYY HH:MI:SS:FF but how do you get the AM/PM on it?
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16

You're close Try 'MM/DD/YYYY HH:MI:SS.FF PM'.


[My Blog]
All roads lead to JavaRanch
Lavanya Halliwell
Ranch Hand

Joined: Apr 09, 2010
Posts: 57
Christophe, thanks for the reply, PM obviously works great for both. what I had read was that you could append 'TT' or 'tt' or 'AM (or PM)' or 'am/pm', but none of those seemed to work.



your post helps me print our ALL records in the given format:

select TO_CHAR(processing_starttm, 'MM/DD/YYYY HH:MI:SS.FF PM') from SRVC_TXN_DATA


But when I create the statement below I get a date format error.
do you know why I would be getting a date format error when I use the same format as yours but in a "between" clause?
I am trying to get all records between timestamp of sysdate and 7 days prior.

SELECT operaton_name,user_id,message_id,status,processing_time FROM SRVC_TXN_DATA WHERE TO_CHAR(processing_starttm, 'MM/DD/YYYY HH:MI:SS.FF PM') BETWEEN TO_CHAR(sysdate, 'MM/DD/YYYY HH:MI:SS.FF PM') AND TO_CHAR((sysdate-90), 'MM/DD/YYYY HH:MI:SS.FF PM')



Lavanya
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16

Use systimestamp instead of sysdate.
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16

By the way, why do you need to convert the dates/timestamps into chars ? You can use BETWEEN without having to convert them.
Lavanya Halliwell
Ranch Hand

Joined: Apr 09, 2010
Posts: 57
Christophe,

thanks for the tip. systimestamp is obviously what I needed to use.

you're right, I didn't need to be converting the format using TO_CHAR.

my query was not right either, but after some extensive analysis I managed to get one that works I think. (only time and debugging will help me know)

Lavanya
 
 
subject: so I have a sql date format I can't figure out