Win a copy of Modern JavaScript for the Impatient this week in the Server-Side JavaScript and NodeJS forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

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

 
Ranch Hand
Posts: 57
  • 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?
 
Sheriff
Posts: 14691
16
Eclipse IDE VI Editor Ubuntu
  • 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
  • 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
Posts: 14691
16
Eclipse IDE VI Editor Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use systimestamp instead of sysdate.
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE VI Editor Ubuntu
  • 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
  • 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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
    Bookmark Topic Watch Topic
  • New Topic