Query to get all days of a month

• ramsatish

  Old Hand

  Points: 380

  More actions

  December 11, 2008 at 7:20 am

  #126407

  Hi All,

  I searched the query to get all days of a month.

  DECLARE @FirstDay smalldatetime, @NumberOfMonths int

  SELECT @FirstDay = '20080201', @NumberOfMonths = 1

  ;WITH Days AS (

  SELECT @FirstDay as CalendarDay

  UNION ALL

  SELECT DATEADD(d, 1, CalendarDay) as CalendarDay

  FROM Days

  WHERE DATEADD(d, 1, CalendarDay) < DATEADD(m, @NumberOfMonths, @FirstDay)

  )

  SELECT CONVERT(char(8), CalendarDay, 112) FROM Days

  Its working fine,but I dont understood how it is working.

  Could u plz explain how this query is working

  Thanks in advance

  Regards,

  Sriram Satish

• Madhivanan-208264

  SSCertifiable

  Points: 7516

  More actions

  December 11, 2008 at 7:35 am

  #911073

  SELECT DATEADD(d, 1, CalendarDay) as CalendarDay

  FROM Days

  WHERE DATEADD(d, 1, CalendarDay) < DATEADD(m, @NumberOfMonths, @FirstDay)

  Means add 1 day to existing day until it is less than the first day of next month

  ---

  Madhivanan

  Failing to plan is Planning to fail

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  December 11, 2008 at 12:42 pm

  #911288

  The CTE "Days" is an example of a "Recursive" query. Just search the word "recursive" in the search block above and you will find a number of articles explaining how they work.

  Simply put, the first query is an "anchor" that sets a starting point and the second query following the UNION ALL is repeated over and over again.

  __________________________________________________

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