더미 테이블 만드실때 쓰세요
1년이든 10년 이든 비교적 금방 만들어요 ^^
그리고 그날의 요일도 있씁니다.
^^ 그럼 즐 쿼리 하십시요
-- 평션 만들어 놓기
CREATE FUNCTION dbo.UFN_showdate(@time datetime)
RETURNS char(14)
AS
BEGIN
DECLARE @date char(14)
SELECT @date=
left(replace(replace(convert(char(20),@time,120),':',''),'
',''),10)
RETURN( @date);
END
GO
-- drop function dbo.UFN_showdate
drop table dateDummyTable
go
create table dateDummyTable
(
idx int identity
, dummyDate char(10)
, dw int
)
go
insert into dateDummyTable select cast(dbo.ufn_showdate(getdate())
as char(10)), datepart(dw,getdate())
go
declare @dummyDateValue char(8)
declare @convertedDate datetime
declare @dw int
declare @i int
set @i = 1
while @i < 365
begin
select @convertedDate = convert(datetime , max(dummyDate),120)
from dateDummyTable
select @convertedDate
set @convertedDate = @convertedDate + 1
print @convertedDate
set @convertedDate= convert(datetime, @convertedDate,120)
insert into dateDummyTable
select cast(dbo.ufn_showdate(@convertedDate) as char(10)),
datepart(dw,@convertedDate)
set @i = @i + 1
end
잘들어 갔나 확인하기
select * from dateDummyTable
|