count(*) returning incorrect value

• auaiomrn

  Ten Centuries

  Points: 1300

  More actions

  February 8, 2008 at 11:54 am

  #182478

  Hello, I had a strange problem and am wondering if anyone can tell me what could have caused it. Running these queries on a certain table gave the following results:

  select count(*) from Table1

  -> about 19 million

  select count(*) from Table1 where DateColumn < 'Jan 31, 2008'

  -> about 10.5 million

  select count(*) from Table1 where DateColumn >= 'Jan 31, 2008'

  -> about 10.5 million

  (there are actually only 2 different dates in DateColumn, one from the beginning of January and one from the beginning February)

  note: DateColumn is part of the primary key

  After reindexing the table the first query returned the correct result of about 21 million. Does anyone know what the problem could have been and how it could have happened?

  Thanks

• Vijaya Kadiyala

  SSCrazy Eights

  Points: 9149

  More actions

  February 15, 2008 at 10:40 am

  #778926

  Hi,

  When you are doing date comparision convert the date to specific format. Can yu post the Table structure with Datatypes.

  Thanks -- Vj

  http://dotnetvj.blogspot.com

  Thanks -- Vijaya Kadiyala
  www.dotnetvj.com
  SQL Server Articles For Beginers

  
  

• greatsun81

  SSC Enthusiast

  Points: 103

  More actions

  February 25, 2008 at 7:24 pm

  #781835

  Hi,topstairs,try like this:

  Create Table table1(

  DateColumn datetime

  )

  Select Count(*) From table1 where convert(varchar(8),DateColumn,112) < '20080226'

  or

  Select Count(*) From table1 where convert(varchar(10),DateColumn,120) < '2008-02-26'