How to count number of days in a column

• josephptran2002

  Hall of Fame

  Points: 3275

  More actions

  December 3, 2008 at 10:29 am

  #127309

  Hi Everyone,

  Like previous question, if I have a column and that column contains dates only.

  If I wish to count the date in each cell as a record (if there is a date in a cell then counts as 1, if not ((cell empty)then count as 0). It does not matter if there is/are a repeated date.

  Please show me how to write code for SQL 2005 since I am new to this.

  here is the code that I wrote in Access Database and converted it to SQL 2005 but when I run it then it says there is a bug in it.

  SELECT Count(IIf(IsNull(Table1![Cleared Date]),0,IIf(Table1![DE_Cleared Date] Between Forms!Form1!txtStart And Forms!Form1!txtEnd,1))) AS 'Total Cleared'

  FROM Table1, Query1;

  Thanks

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  December 3, 2008 at 10:44 am

  #907029

  When you say a date field is "empty", does that mean the column in your table is NULL ?

  If so, you can just take a count of the rows in your date range. (NULLS don't count.)

  ------------------------------------------------------------------------------------------------

  declare @datesTable table (id int identity(1,1), date_column datetime)

  declare @start datetime

  declare @end datetime

  set @start = '1/1/2008'

  set @end = '1/5/2008'

  insert into @datesTable

  select '1/1/2008' union all

  select null union all

  select '1/1/2008' union all

  select null union all

  select '1/2/2008' union all

  select null union all

  select '1/2/2008' union all

  select null union all

  select '1/3/2008' union all

  select '1/4/2008' union all

  select '1/4/2008' union all

  select '1/5/2008' union all

  select '1/6/2008'

  select * from @datesTable

  select count(date_column)

  from @datesTable

  where date_column between @start and @end

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills