1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
| USE [zapsis]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TMPDATE]') AND type in (N'U')) DROP TABLE [dbo].[TMPDATE]
CREATE TABLE dbo.TMPDATE([cardid] [int] NOT NULL,[birthday] [date] NULL,[c_creation_date] [date] NULL,[c_last_use] [date] NULL,[c_expire_date] [date] NULL)
DECLARE @nb AS INT, @i AS INT,@cardid AS INT
DECLARE @birthday AS DATE,@c_creation_date AS DATE,@c_last_use AS DATE,@c_expire_date AS DATE
DECLARE @year AS VARCHAR(4)
DECLARE @mnaiss AS VARCHAR(2),@jnaiss AS VARCHAR(2)
DECLARE @mcrea AS VARCHAR(2),@jcrea AS VARCHAR(2)
DECLARE @mlastuse AS VARCHAR(2),@jlastuse AS VARCHAR(2)
DECLARE @mexpire AS VARCHAR(2),@jexpire AS VARCHAR(2)
SELECT @nb= MAX(dbo.cards.cardid) FROM dbo.cards
SET @i = 1
SET @year= '2012'
WHILE @i <= @nb
BEGIN
if (select cardid from cards WHERE cardid=@i)>0
BEGIN
SELECT @cardid= cardid,@mnaiss= MONTH(birthday),@jnaiss= DAY(birthday), @mcrea= MONTH(c_creation_date),@jcrea= DAY(c_creation_date), @mlastuse= MONTH(c_last_use),@jlastuse= DAY(c_last_use), @mexpire= MONTH(c_expire_date),@jexpire= DAY(c_expire_date) FROM cards WHERE cardid=@i
SET @birthday= cast(@year+'-'+@mnaiss+'-'+@jnaiss AS DATE)
SET @c_creation_date= cast(@year+'-'+@mcrea+'-'+@jcrea AS DATE)
SET @c_last_use= cast(@year+'-'+@mlastuse+'-'+@jlastuse AS DATE)
SET @c_expire_date= cast(@year+'-'+@mexpire+'-'+@jexpire AS DATE)
INSERT INTO dbo.TMPDATE(cardid,birthday,c_creation_date,c_last_use,c_expire_date)VALUES(@cardid,@birthday,@c_creation_date,@c_last_use,@c_expire_date)
END
SET @i = @i + 1
END |
Partager