生成日历

生成日历的存储过程
CREATE PROCEDURE [dbo].[GetCalendar]
    (
      
@InputDateTime DATE = NULL
    )
AS 
    
BEGIN
        
IF @InputDateTime IS NULL 
            
SET @InputDateTime = GETDATE() ;

        
WITH    x ( Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Week, Date )
                  
AS ( SELECT   CASE WHEN DATEPART(dw, thismonth.first) = 1
                                     
THEN DAY(thismonth.FIRST)
                                     
ELSE NULL
                                
END ,
                                
CASE WHEN DATEPART(dw, thismonth.first) = 2
                                     
THEN DAY(thismonth.FIRST)
                                     
ELSE NULL
                                
END ,
                                
CASE WHEN DATEPART(dw, thismonth.first) = 3
                                     
THEN DAY(thismonth.FIRST)
                                     
ELSE NULL
                                
END ,
                                
CASE WHEN DATEPART(dw, thismonth.first) = 4
                                     
THEN DAY(thismonth.FIRST)
                                     
ELSE NULL
                                
END ,
                                
CASE WHEN DATEPART(dw, thismonth.first) = 5
                                     
THEN DAY(thismonth.FIRST)
                                     
ELSE NULL
                                
END ,
                                
CASE WHEN DATEPART(dw, thismonth.first) = 6
                                     
THEN DAY(thismonth.FIRST)
                                     
ELSE NULL
                                
END ,
                                
CASE WHEN DATEPART(dw, thismonth.first) = 7
                                     
THEN DAY(thismonth.FIRST)
                                     
ELSE NULL
                                
END ,
                                
DATEPART(WK, thismonth.first) ,
                                thismonth.first
                       
FROM     ( SELECT    DATEADD(D,
                                                    
-DATEPART(d,
                                                              
@InputDateTime)
                                                    
+ 1@InputDateTimeAS first
                                ) 
AS thismonth
                       
UNION ALL
                       
SELECT   CASE WHEN DATEPART(dw, DATEADD(d, 1, x.Date)) = 1
                                     
THEN DAY(DATEADD(d, 1, x.Date))
                                     
ELSE NULL
                                
END ,
                                
CASE WHEN DATEPART(dw, DATEADD(d, 1, x.Date)) = 2
                                     
THEN DAY(DATEADD(d, 1, x.Date))
                                     
ELSE NULL
                                
END ,
                                
CASE WHEN DATEPART(dw, DATEADD(d, 1, x.Date)) = 3
                                     
THEN DAY(DATEADD(d, 1, x.Date))
                                     
ELSE NULL
                                
END ,
                                
CASE WHEN DATEPART(dw, DATEADD(d, 1, x.Date)) = 4
                                     
THEN DAY(DATEADD(d, 1, x.Date))
                                     
ELSE NULL
                                
END ,
                                
CASE WHEN DATEPART(dw, DATEADD(d, 1, x.Date)) = 5
                                     
THEN DAY(DATEADD(d, 1, x.Date))
                                     
ELSE NULL
                                
END ,
                                
CASE WHEN DATEPART(dw, DATEADD(d, 1, x.Date)) = 6
                                     
THEN DAY(DATEADD(d, 1, x.Date))
                                     
ELSE NULL
                                
END ,
                                
CASE WHEN DATEPART(dw, DATEADD(d, 1, x.Date)) = 7
                                     
THEN DAY(DATEADD(d, 1, x.Date))
                                     
ELSE NULL
                                
END ,
                                
DATEPART(WK, DATEADD(d, 1, x.Date)) ,
                                
DATEADD(d, 1, x.Date)
                       
FROM     x
                       
WHERE    MONTH(DATEADD(d, 1, x.Date)) = MONTH(x.Date)
                     )
            
SELECT  MAX(x.Sunday) AS Sunday ,
                    
MAX(x.Monday) AS Monday ,
                    
MAX(x.Tuesday) AS Tuesday ,
                    
MAX(x.Wednesday) AS Wednesday ,
                    
MAX(x.Thursday) AS Thursday ,
                    
MAX(x.Friday) AS Friday ,
                    
MAX(x.Saturday) AS Saturday
            
FROM    x
            
GROUP BY Week
            
ORDER BY Week
    
END    

GO


posted @ 2009-11-08 16:49  木子二鸟  阅读(408)  评论(0编辑  收藏  举报