用Procedure取得全月的日期

来源：互联网发布：中国公知奇葩言论编辑：程序博客网时间：2024/06/05 15:51

方法1：

CREATE Procedure xl_ShinchokuWeek@tyosaDT varchar(7)AsDECLARE @tblMonthTyosa TABLE([tyosaDT][varchar](14),[cntTyosaJishi][int] ,[cntTyosain][int],[cntTensaku1][int],[cntTensaku2][int],[cntCount][int],--データの順番   [cntWeek][int],--週目[cntGokei][int]--合計フラグ ０：普通の時間、１：週間合計、２：月間合計) DECLARE@datefirstdatetime   DECLARE@datelastdatetimeDECLARE@dateCntdatetimeDECLARE@countintDECLARE@weekintDECLARE@groupint--計算用    Set @count = 1Set @week = 1Set @group = 1/*開始時間*/Set @datefirst = cast(@tyosaDT + '/01' as datetime)    while datepart(dw,@datefirst) <> 2Begin Set @datefirst = datediff(day,1,@datefirst)End/*結束時間*/Set @datelast = cast(datediff(d,1,dateadd(m,1,cast(@tyosaDT + '/01' as datetime))) as datetime)    while datepart(dw,@datelast) <> 1Begin Set @datelast = dateadd(day,1,@datelast)End/*時間*/Set @dateCnt = @datefirstWHILE @dateCnt <= @datelastBegin     --普通のデータInsert into @tblMonthTyosa(tyosaDT, cntTyosaJishi, cntTyosain, cntTensaku1, cntTensaku2, cntCount, cntWeek, cntGokei)VALUES(substring(CONVERT(char(10),@dateCnt,111),1,10) + CASE datepart(dw,@dateCnt) WHEN 1 THEN '(日)' WHEN 2 THEN '(月)'  WHEN 3 THEN '(火)'  WHEN 4 THEN '(水)'  WHEN 5 THEN '(木)'  WHEN 6 THEN '(金)'  WHEN 7 THEN '(土)' END, 0, 0, 0, 0, @count, @week, 0)Set @count = @count + 1Set @group = @group + 1IF @group =8Begin--週間合計Insert into @tblMonthTyosa(tyosaDT, cntTyosaJishi, cntTyosain, cntTensaku1, cntTensaku2, cntCount, cntWeek, cntGokei)VALUES('週間合計', 0, 0, 0, 0, @count, @week, 1)Set @count = @count + 1Set @group = 1Set @week = @week + 1EndSet @dateCnt = dateadd(d,1,@dateCnt)EndWHILE @dateCnt > @datelast and @count<>0Begin     --月間合計Insert into @tblMonthTyosa(tyosaDT, cntTyosaJishi, cntTyosain, cntTensaku1, cntTensaku2, cntCount, cntWeek, cntGokei)VALUES('月間合計', 0, 0, 0, 0, @count, 0, 2)Set @count = 0EndSELECT  *FROM @tblMonthTyosa  ORDER BY cntCountreturnGO

方法2：

drop proc xl_tempgoCREATE Proc xl_Temp@tyosaDT varchar(7),@countint output   ,@temp varchar output,@datetime datetime outputAsbeginDECLARE @tblMonthTyosa TABLE([tyosaDT][varchar](14),[cntTyosaJishi][int] ,[cntTyosain][int],[cntTensaku1][int],[cntTensaku2][int]) DECLARE@datefirstdatetime   DECLARE@datelastdatetimeDECLARE@dateCntdatetime/*DECLARE@countint   */    Set @count = 0/*開始時間*/Set @datefirst = cast(@tyosaDT + '/01' as datetime)    while datepart(dw,@datefirst) <> 2Begin Set @datefirst = cast(datediff(day,1,@datefirst) as datetime)End/*結束時間*/Set @datelast = cast(datediff(d,1,dateadd(m,1,cast(@tyosaDT + '/01' as datetime))) as datetime)    while datepart(dw,@datelast) <> 1Begin Set @datelast = cast(dateadd(day,1,@datelast) as datetime)End/*時間*/Set @dateCnt = @datefirstSet @datetime = @datelastWHILE @dateCnt <= @datelastBegin     Insert into @tblMonthTyosa(tyosaDT)VALUES(substring(CONVERT(char(10),@dateCnt,111),1,10) + CASE datepart(dw,@dateCnt) WHEN 1 THEN '(日)' WHEN 2 THEN '(月)'  WHEN 3 THEN '(火)'  WHEN 4 THEN '(水)'  WHEN 5 THEN '(木)'  WHEN 6 THEN '(金)'  WHEN 7 THEN '(土)' END)Set @count = @count +1Set @dateCnt = dateadd(d,1,@dateCnt)EndSELECT  *FROM @tblMonthTyosa  ORDER BY tyosaDTendgodeclare @cnttemp intdeclare @temp varchardeclare @datetime datetimeexec xl_Temp '2008/09',@cnttemp output,@temp output,@datetime outputprint @cnttempprint @tempprint @datetime

0 0