File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Lavanya Halliwell
Ranch Hand
Posts: 57
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're close Try 'MM/DD/YYYY HH:MI:SS.FF PM'.
 
Lavanya Halliwell
Ranch Hand
Posts: 57
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use systimestamp instead of sysdate.
 
Christophe Verré
Sheriff
Pie
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 57
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic