DateDiff and Tally Table

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  May 6, 2008 at 7:59 am

  #71502

  All,

  I am facing a problem while using Tally table.

  SELECT SP,Year,Month,DATEADD(DAY,-1,DATEADD(MONTH,(N),DateStart)) AS x

  FROM #A1,Tally

  WHERE N <= case when DATEDIFF(MONTH,DateStart,DateEnd)

  Assume that DateStart and DateEnd are equal.

  say for example,

  DateStart = '01/Apr/2008'

  DateEnd = '15/Apr/2008'

  In these kind of situation, the above query is not showing the last date of that particular date.

  I have executed

  Select DateDiff(Month,'01/Apr/2008','15/Apr/2008')

  I got '0' as the output.

  Coming back to the first query, Tally table's running number starting with 1 not '0'.

  Thats why it is displaying nothing in the result panel.

  Ok,Now my requirement is,

  if i have the same month in DateStart & DateEnd column, how should i display that month's LastDate ?

  Inputs are welcome!

  karthik

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  May 6, 2008 at 8:16 am

  #811224

  I have modifed the code as below

  SELECT SP,Year,Month,DATEADD(DAY,-1,DATEADD(MONTH,(N),DateStart)) AS x

  FROM #A1,Tally

  WHERE N-1 <= case when DATEDIFF(MONTH,DateStart,DateEnd)

  It is working fine now.

  karthik