Xem ti vi online

Design by Blogcrowds

Monday, May 2, 2011

Get all day in month

Posted by Duc Nguyen at 2:31 AM

--**********************
DECLARE @FirstDay smalldatetime, @NumberOfMonths int
SELECT @FirstDay = '2008-02-01', @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
--**********************

Labels:

1 Comment:

  1. Duc Nguyen said...
    May 2, 2011 at 2:32 AM
    DECLARE @beginDate datetime
    DECLARE @endDate datetime
    DECLARE @counter INT
    DECLARE @difference INT
    DECLARE @newdate SMALLDATETIME
    DECLARE @tempDate TABLE
    (
    [week] int,
    [date] smalldatetime
    )
    -- test parameters
    SET @begindate = '01/01/2007'
    SET @enddate = '01/15/2007'

    SELECT @difference = DATEDIFF(day, @beginDate,@endDate)
    SET @counter = 0
    WHILE @counter <= @difference
    BEGIN
    BEGIN
    SET @newdate = DATEADD(day,@counter,@beginDate)
    INSERT INTO @tempDate VALUES (DATEPART(week, @newDate), @newDate)
    END
    SET @counter = @counter + 1
    END

    SELECT * FROM @tempDate

Post a Comment



Subscribe to: Post Comments (Atom)