[RESOLVED] Select all dates in a givent month with T-SQL

[stricknyn]

Hello all,

Is there a way using T-SQL that I can select all dates in a given month? I do not want to put the dates in a table and select them that way. I want to be able to say:

Select 112007

(for example and 30 dates are returned.)

11/1/07
11/2/07
11/3/07
'etc.

can this be done?

Thanks,

Strick

[brucevde]

You will need a Table, a Select statement will not work.

A simple loop that loads a Table variable (ver 2000 or 2005 - use a temp table in sql 7.0 ) and then selects from the table variable.

Declare @Days Table (DateField datetime)

Declare @CurrentDate datetime
Declare @EndDate datetime

Set @CurrentDate = '01-Nov-2007'
Set @EndDate = '30-Nov-2007'

While @CurrentDate <= @EndDate
  Begin
     Insert Into @Days Values(@CurrentDate)
     Set @CurrentDate = DateAdd(d,1,@CurrentDate)
  End 

Select * From @Days

[stricknyn]

Nice thanks! I'll try this out.

[nagaraju.jammugani]

hello all
is there is way to get all dates deatils in month when i give month as input

Thank you sir

[GaryMazzone]

Set the first of the month based on the passed in parameter:

11 Passed in

Set the first of the month
Declare @firstOfMonth DateTime
Set @firstOfMonth = Convert(NVarchar(2),@Passed) + '/1/' + convert(NVarchar(4),Year(GetUTCDate))

--Find the first of the next month using DateAdd and subtract 1 day
Declare @lastOfMonth DateTime
Set @lastOfMonth = DateAdd(dd,-1,DateAdd(mm,1,@firstOfMonth))

No do what was shown in post number 2