数据生成时间表

?

TRUNCATE TABLE DateNorm

go

DECLARE @DateJ INT

DECLARE @CurDate DATETIME

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = '2004-1-1'

 ---------这里填写起始时间

SET @EndDate = '2025-1-1'

 ---------这里填写结束时间

 --SELECT DATEDIFF(day, @StartDate, @EndDate) into @DateJ

   

SET @CurDate = @StartDate

WHILE @CurDate < @EndDate

    BEGIN

        INSERT  INTO DateNorm

                ( TheDate ,

                  TheDay ,

                  Quarter ,

                  TendayOfMonth ,

                  HalfYear ,

                  WeekNorm

                )

        VALUES  ( @CurDate ,

                  CASE WHEN DATEPART(weekday, @CurDate) = 1 THEN '星期日'

                       WHEN DATEPART(weekday, @CurDate) = 2 THEN '星期一'

                       WHEN DATEPART(weekday, @CurDate) = 3 THEN '星期二'

                       WHEN DATEPART(weekday, @CurDate) = 4 THEN '星期三'

                       WHEN DATEPART(weekday, @CurDate) = 5 THEN '星期四'

                       WHEN DATEPART(weekday, @CurDate) = 6 THEN '星期五'

                       WHEN DATEPART(weekday, @CurDate) = 7 THEN '星期六'

                  END ,

                  DATENAME(quarter, @CurDate) ,

                  CASE WHEN DATEPART(day, @CurDate) <= 10 THEN '上旬'

                       WHEN DATEPART(day, @CurDate) <= 20

                            AND DATEPART(day, @CurDate) > 10 THEN '中旬'

                       WHEN DATEPART(day, @CurDate) > 20 THEN '下旬'

                  END ,

                  CASE WHEN DATEPART(month, @CurDate) <= 6 THEN '上半年'

                       WHEN DATEPART(month, @CurDate) > 6 THEN '下半年'

                  END ,

                  NULL

                )

        SET @CurDate = DATEADD(day, 1, @CurDate)

    END

     

  /*

        周初始化

  */

 

DECLARE @FirstDay DATETIME

DECLARE @TheDayTh INT

DECLARE @addDays INT

DECLARE @beginDate DATETIME

DECLARE @FinishDate DATETIME

DECLARE @TheDay NVARCHAR(15)

DECLARE @TheDate DATETIME

 

DECLARE C CURSOR

FOR

    SELECT  TheDate ,

            TheDay

    FROM    DateNorm

OPEN C

FETCH NEXT FROM C INTO @TheDate, @TheDay

WHILE @@fetch_status = 0

    BEGIN

        SET @FirstDay = DATEADD(mm, DATEDIFF(mm, 0, @TheDate), 0)

        IF ( @TheDate = @FirstDay )

            BEGIN

                SET @TheDayTh = 1

                IF ( @TheDay = '星期日' )

                    SET @addDays = 0

                ELSE

                    IF ( @TheDay = '星期六' )

                        SET @addDays = 1

                    ELSE

                        IF ( @TheDay = '星期五' )

                            SET @addDays = 2

                        ELSE

                            IF ( @TheDay = '星期四' )

                                SET @addDays = 3

                            ELSE

                                IF ( @TheDay = '星期三' )

                                    SET @addDays = 4

                                ELSE

                                    IF ( @TheDay = '星期二' )

                                        SET @addDays = 5

                                    ELSE

                                        IF ( @TheDay = '星期一' )

                                            SET @addDays = 6

              

                SET @beginDate = @TheDate

                SET @FinishDate = DATEADD(dd, @addDays, @beginDate)

                                               

                UPDATE  DateNorm

                SET     WeekNorm = @TheDayTh

                WHERE   TheDate BETWEEN @beginDate AND @FinishDate

            END

        ELSE

            BEGIN

                IF ( @TheDate > @FinishDate )

                    BEGIN

                        SET @TheDayTh = @TheDayTh + 1

                        IF ( @TheDay = '星期日' )

                            SET @addDays = 0

                        ELSE

                            IF ( @TheDay = '星期六' )

                                SET @addDays = 1

                            ELSE

                                IF ( @TheDay = '星期五' )

                                    SET @addDays = 2

                                ELSE

                                    IF ( @TheDay = '星期四' )

                                        SET @addDays = 3

                                    ELSE

                                        IF ( @TheDay = '星期三' )

                                            SET @addDays = 4

                                        ELSE

                                            IF ( @TheDay = '星期二' )

                                                SET @addDays = 5

                                            ELSE

                                                IF ( @TheDay = '星期一' )

                                                    SET @addDays = 6

                 

                        SET @beginDate = @TheDate

                        SET @FinishDate = DATEADD(dd, @addDays, @beginDate)

                             

                        UPDATE  DateNorm

                        SET     WeekNorm = @TheDayTh

                        WHERE   TheDate BETWEEN @beginDate AND @FinishDate

                    END

            END

             

        FETCH NEXT FROM C INTO @TheDate, @TheDay

    END

CLOSE C

DEALLOCATE C