Home » SQL & PL/SQL » SQL & PL/SQL » Count without time stamps
Count without time stamps [message #259526] Wed, 15 August 2007 19:29 Go to next message
pradkuamr
Messages: 29
Registered: November 2006
Junior Member
Hi All

I am having a table which is having a date column(time stamp). Sometimes in this column only date is getting populated without time stamp. I need a count of how many records are there without a time stamp. The data looks like:

ID DATE
1 7/1/2007 08:04:45 PM
2 7/2/2007
3 7/24/2007 17:30:00 PM

So my requirement is to get a count on date without time stamp. In this case its "1"

I tried using the following query:
select count(*) from table where date between TO_DATE('2007-07-01','YYYY-MM-DD') and TO_DATE('2007-07-31','YYYY-MM-DD')

But it showed up even the time stamps.
Please could anyone share their ideas

Thanks
Re: Count without time stamps [message #259527 is a reply to message #259526] Wed, 15 August 2007 19:37 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
I suspect that you are not accurately reporting reality.
use CUT & PASTE along with PROPER formatting of your input & Oracle's responses as stated in STICKY post at top of forum.
Include the results from SQL*Plus of
SQL> DESC <table_name_here>
I seriously doubt the actual table_name is 'table'.
Re: Count without time stamps [message #259531 is a reply to message #259527] Wed, 15 August 2007 21:31 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
select count(*) 
from table 
where date <> trunc(date)


Ross Leishman
Re: Count without time stamps [message #259576 is a reply to message #259531] Thu, 16 August 2007 00:37 Go to previous message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rleishman wrote on Thu, 16 August 2007 04:31
select count(*) 
from table 
where date <> trunc(date)


Ross Leishman

You meant:
select count(*) 
from table 
where date = trunc(date)

Regards
Michel


Previous Topic: Reset table data after a process
Next Topic: CLOB DATA TYPE PROBLEM
Goto Forum:
  


Current Time: Thu Dec 08 04:04:49 CST 2016

Total time taken to generate the page: 0.09957 seconds