IF(DATEPART(YEAR,GETDATE()) <> (SELECT ISNULL(SUBSTRING(MAX(StartDate),1,4),'1900') FROM #Weeks)) BEGIN DECLARE @StartDate CHAR(8) DECLARE @EndDate CHAR(8) DECLARE @Num INT = 1 DECLARE @Month CHAR(2)
IF NOT EXISTS(SELECT 1 FROM #Weeks) BEGIN SET @StartDate = CAST(DATEPART(Year,GETDATE()) AS VARCHAR) + '01' + '01' END ELSE BEGIN SET @StartDate = (SELECT MAX(EndDate) FROM #Weeks) SET @StartDate = CONVERT(CHAR(8),DATEADD(D,1,CAST(@StartDate AS DATETIME)),112) END
IF SUBSTRING(@StartDate,1,4) = (SELECT SUBSTRING(MAX(WeekCode),1,4) FROM #Weeks) BEGIN SET @EndDate = CAST(DATEPART(Year,DATEADD(YEAR,1,CAST(@StartDate AS DATETIME))) AS VARCHAR(8)) + '1231' SET @Num = CAST((SELECT SUBSTRING(MAX(WeekCode),7,1) FROM #Weeks) AS INT) + 1 END ELSE BEGIN SET @EndDate = SUBSTRING(@StartDate,1,4) + '1231' END
WHILE DATEPART(W,CAST(@StartDate AS DATETIME)) <> 5 BEGIN SET @StartDate = CONVERT(CHAR(8),DATEADD(D,1,CAST(@StartDate AS DATETIME)),112) END
DECLARE @CurrenMonth CHAR(2) SET @CurrenMonth = REPLICATE('0',2-LEN(DATEPART(MM,CAST(@StartDate AS DATETIME)))) + CAST(DATEPART(MM,CAST(@StartDate AS DATETIME)) AS VARCHAR)
WHILE CONVERT(CHAR(8),DATEADD(Day,6,CAST(@StartDate AS DATETIME)),112) <= @EndDate BEGIN SET @Month = REPLICATE('0',2-LEN(DATEPART(MM,CAST(@StartDate AS DATETIME)))) + CAST(DATEPART(MM,CAST(@StartDate AS DATETIME)) AS VARCHAR)
IF(@CurrenMonth <> @Month) BEGIN SET @Num = 1 END
INSERT INTO #Weeks SELECT SUBSTRING(@StartDate,1,4)+@Month + CAST(@Num AS VARCHAR) ,@StartDate ,CONVERT(CHAR(8),DATEADD(Day,6,CAST(@StartDate AS DATETIME)),112)
SET @StartDate = CONVERT(CHAR(8),DATEADD(Day,7,CAST(@StartDate AS DATETIME)),112) SET @CurrenMonth = @Month SET @Num = @Num + 1
--标记示例: WITH a1 ([1],[2],[3],[4],[5]) AS ( SELECT 0,0,3,0,0 )SELECT CASE [1] WHEN 0 THEN '' ELSE 'V' END, CASE [2] WHEN 0 THEN '' ELSE 'V' END, CASE [3] WHEN 0 THEN '' ELSE 'V' END, CASE [4] WHEN 0 THEN '' ELSE 'V' END, CASE [5] WHEN 0 THEN '' ELSE 'V' END FROM a1
select DATEPART(wk,getdate())-DATEPART(wk,DATEADD(dd,-day(getdate()),getdate())) + 1 as wk,
datename(weekday,getdate()) as weekday
set @date=GETDATE()
select DATEPART(WEEK,@date)-DATEPART(WEEK,DATEADD(month,datediff(month,0,@date),0))+1
set @date ='2013-11-30'
select @date as 目前時間,
DATENAME (W,@date ) as 星期,
DATEPART (w,@date)-1 as 星期,
DATEPART (WK,@date )-datepart(wk,DATEADD(mm, DATEDIFF(mm,0,@date ), 0))+1 as 本月周
WITH a1 (projectname,datetime1,datetime2,datetime3,datetime4) AS
(
SELECT '项目A','2013-12-02','2013-12-10','2013-12-18','2013-12-26'
)
select projectname,
cast(datepart(mm,datetime1) as varchar(2)) + '月第' + cast((datepart(wk,datetime1) - datepart(wk,convert(varchar(7),datetime1,120) + '-01') + 1) as varchar(2)) + '周','周'+RTRIM(DATEPART(WEEKDAY,datetime1)-1),
cast(datepart(mm,datetime2) as varchar(2)) + '月第' + cast((datepart(wk,datetime2) - datepart(wk,convert(varchar(7),datetime2,120) + '-01') + 1) as varchar(2)) + '周','周'+RTRIM(DATEPART(WEEKDAY,datetime2)-1),
cast(datepart(mm,datetime3) as varchar(2)) + '月第' + cast((datepart(wk,datetime3) - datepart(wk,convert(varchar(7),datetime3,120) + '-01') + 1) as varchar(2)) + '周','周'+RTRIM(DATEPART(WEEKDAY,datetime3)-1),
cast(datepart(mm,datetime4) as varchar(2)) + '月第' + cast((datepart(wk,datetime4) - datepart(wk,convert(varchar(7),datetime4,120) + '-01') + 1) as varchar(2)) + '周','周'+RTRIM(DATEPART(WEEKDAY,datetime4)-1)
FROM a1
CREATE TABLE #Weeks(
WeekCode CHAR(7)
,StartDate CHAR(8)
,EndDate CHAR(8)
)
IF(DATEPART(YEAR,GETDATE()) <> (SELECT ISNULL(SUBSTRING(MAX(StartDate),1,4),'1900') FROM #Weeks))
BEGIN
DECLARE @StartDate CHAR(8)
DECLARE @EndDate CHAR(8)
DECLARE @Num INT = 1
DECLARE @Month CHAR(2)
IF NOT EXISTS(SELECT 1 FROM #Weeks)
BEGIN
SET @StartDate = CAST(DATEPART(Year,GETDATE()) AS VARCHAR) + '01' + '01'
END
ELSE
BEGIN
SET @StartDate = (SELECT MAX(EndDate) FROM #Weeks)
SET @StartDate = CONVERT(CHAR(8),DATEADD(D,1,CAST(@StartDate AS DATETIME)),112)
END
IF SUBSTRING(@StartDate,1,4) = (SELECT SUBSTRING(MAX(WeekCode),1,4) FROM #Weeks)
BEGIN
SET @EndDate = CAST(DATEPART(Year,DATEADD(YEAR,1,CAST(@StartDate AS DATETIME))) AS VARCHAR(8)) + '1231'
SET @Num = CAST((SELECT SUBSTRING(MAX(WeekCode),7,1) FROM #Weeks) AS INT) + 1
END
ELSE
BEGIN
SET @EndDate = SUBSTRING(@StartDate,1,4) + '1231'
END
WHILE DATEPART(W,CAST(@StartDate AS DATETIME)) <> 5
BEGIN
SET @StartDate = CONVERT(CHAR(8),DATEADD(D,1,CAST(@StartDate AS DATETIME)),112)
END
DECLARE @CurrenMonth CHAR(2)
SET @CurrenMonth = REPLICATE('0',2-LEN(DATEPART(MM,CAST(@StartDate AS DATETIME)))) + CAST(DATEPART(MM,CAST(@StartDate AS DATETIME)) AS VARCHAR)
WHILE CONVERT(CHAR(8),DATEADD(Day,6,CAST(@StartDate AS DATETIME)),112) <= @EndDate
BEGIN
SET @Month = REPLICATE('0',2-LEN(DATEPART(MM,CAST(@StartDate AS DATETIME)))) + CAST(DATEPART(MM,CAST(@StartDate AS DATETIME)) AS VARCHAR)
IF(@CurrenMonth <> @Month)
BEGIN
SET @Num = 1
END
INSERT INTO #Weeks
SELECT SUBSTRING(@StartDate,1,4)+@Month + CAST(@Num AS VARCHAR)
,@StartDate
,CONVERT(CHAR(8),DATEADD(Day,6,CAST(@StartDate AS DATETIME)),112)
SET @StartDate = CONVERT(CHAR(8),DATEADD(Day,7,CAST(@StartDate AS DATETIME)),112)
SET @CurrenMonth = @Month
SET @Num = @Num + 1
END
END
--DROP TABLE #Weeks
/*
WeekCode StartDate EndDate
2013011 20130103 20130109
2013012 20130110 20130116
2013013 20130117 20130123
2013014 20130124 20130130
2013015 20130131 20130206
2013021 20130207 20130213
2013022 20130214 20130220
2013023 20130221 20130227
2013024 20130228 20130306
2013031 20130307 20130313
2013032 20130314 20130320
2013033 20130321 20130327
2013034 20130328 20130403
2013041 20130404 20130410
2013042 20130411 20130417
2013043 20130418 20130424
2013044 20130425 20130501
2013051 20130502 20130508
2013052 20130509 20130515
2013053 20130516 20130522
2013054 20130523 20130529
2013055 20130530 20130605
2013061 20130606 20130612
2013062 20130613 20130619
2013063 20130620 20130626
2013064 20130627 20130703
2013071 20130704 20130710
2013072 20130711 20130717
2013073 20130718 20130724
2013074 20130725 20130731
2013081 20130801 20130807
2013082 20130808 20130814
2013083 20130815 20130821
2013084 20130822 20130828
2013085 20130829 20130904
2013091 20130905 20130911
2013092 20130912 20130918
2013093 20130919 20130925
2013094 20130926 20131002
2013101 20131003 20131009
2013102 20131010 20131016
2013103 20131017 20131023
2013104 20131024 20131030
2013105 20131031 20131106
2013111 20131107 20131113
2013112 20131114 20131120
2013113 20131121 20131127
2013114 20131128 20131204
2013121 20131205 20131211
2013122 20131212 20131218
2013123 20131219 20131225
*/
--标记示例:
WITH a1 ([1],[2],[3],[4],[5]) AS
(
SELECT 0,0,3,0,0
)SELECT
CASE [1] WHEN 0 THEN '' ELSE 'V' END,
CASE [2] WHEN 0 THEN '' ELSE 'V' END,
CASE [3] WHEN 0 THEN '' ELSE 'V' END,
CASE [4] WHEN 0 THEN '' ELSE 'V' END,
CASE [5] WHEN 0 THEN '' ELSE 'V' END
FROM a1