DECLARE @T TABLE(A VARCHAR(10)) DECLARE @D VARCHAR(8) DECLARE @I INT SET @D = CONVERT(VARCHAR, GETDATE(), 112) SET @I = 1 SET NOCOUNT ON WHILE @I <= 7 BEGIN INSERT INTO @T VALUES(@D + RIGHT('00' + CAST(@I AS VARCHAR), 2)) SET @I = @I + 1 END SELECT * FROM @T SET NOCOUNT OFF效果 ---------- 2006110901 2006110902 2006110903 2006110904 2006110905 2006110906 2006110907
declare @t table(code varchar(10)) declare @i int set @i=1while @i<10 begin insert into @t(code) select convert(char(8),getdate(),112)+right('0'+rtrim(isnull(max(right(code,2)),0)+1),2) from @t where left(code,8)=convert(char(8),getdate(),112)
set @i=@i+1 endselect * from @t/* code ---------- 2006110901 2006110902 2006110903 2006110904 2006110905 2006110906 2006110907 2006110908 2006110909 */
楼主,我下面的代码是在做时间维表的时候写的一个Job,里面有一个字段就是像你要的这样的,提供你参考一下 :) ======================================Set NoCount On Go Set Language 简体中文 Go Delete DimTime GoDeclare @BDate DateTime Declare @EDate DateTime Set @BDate = '2002-01-01 00:00:00' --Set @EDate = '2002-1-01 18:59:59' Set @EDate = '2008-12-31 23:59:59'Declare @oDate DateTime Set @oDate = @BDateDeclare @Year int,@Date char(10),@SemiYear Char(1),@Quarter Char(1),@month smallint Declare @DNOfYear smallint,@DNOfMonth smallint,@DNOfWeek smallint Declare @DNameOfWeek nchar(3),@Daytime nchar(1)While DateDiff(hh,@oDate,@EDate) >= 0 Begin Select @year = Year(@oDate) Select @Date = Replace(Replace(Convert(Varchar(13),@oDate,120),'-',''),' ','') Select @SemiYear = Case When Month(@oDate) <= 6 Then '1' Else '2' End Select @Quarter = DatePart(qq,@oDate) Select @month = Month(@oDate)
最烂的生成法之一..我想高手们还有更多其他生成法.
比如+@@identity
DECLARE @D VARCHAR(8)
DECLARE @I INT
SET @D = CONVERT(VARCHAR, GETDATE(), 112)
SET @I = 1
SET NOCOUNT ON
WHILE @I <= 7
BEGIN
INSERT INTO @T VALUES(@D + RIGHT('00' + CAST(@I AS VARCHAR), 2))
SET @I = @I + 1
END
SELECT * FROM @T
SET NOCOUNT OFF效果
----------
2006110901
2006110902
2006110903
2006110904
2006110905
2006110906
2006110907
declare @i int
set @i=1while @i<10
begin
insert into @t(code)
select
convert(char(8),getdate(),112)+right('0'+rtrim(isnull(max(right(code,2)),0)+1),2)
from
@t
where
left(code,8)=convert(char(8),getdate(),112)
set @i=@i+1
endselect * from @t/*
code
----------
2006110901
2006110902
2006110903
2006110904
2006110905
2006110906
2006110907
2006110908
2006110909
*/
======================================Set NoCount On
Go
Set Language 简体中文
Go
Delete DimTime
GoDeclare @BDate DateTime
Declare @EDate DateTime
Set @BDate = '2002-01-01 00:00:00'
--Set @EDate = '2002-1-01 18:59:59'
Set @EDate = '2008-12-31 23:59:59'Declare @oDate DateTime
Set @oDate = @BDateDeclare @Year int,@Date char(10),@SemiYear Char(1),@Quarter Char(1),@month smallint
Declare @DNOfYear smallint,@DNOfMonth smallint,@DNOfWeek smallint
Declare @DNameOfWeek nchar(3),@Daytime nchar(1)While DateDiff(hh,@oDate,@EDate) >= 0
Begin
Select @year = Year(@oDate)
Select @Date = Replace(Replace(Convert(Varchar(13),@oDate,120),'-',''),' ','')
Select @SemiYear = Case When Month(@oDate) <= 6 Then '1' Else '2' End
Select @Quarter = DatePart(qq,@oDate)
Select @month = Month(@oDate)
Select @DNOfYear = Datepart(dy,@oDate)
Select @DNOfMonth = Day(@oDate)
Select @DNOfWeek = Datepart(dw,@oDate)
Select @DNameOfWeek = DateName(dw,@oDate)
Select @Daytime = Case When Datepart(hh,@oDate) <= 12 Then N'早'
Else Case When Datepart(hh,@oDate) <= 17 Then N'中'
Else N'晚'
End
End
Insert Into DimTime([Year],[Date],[SemiYear],[Quarter],[month],
[DayOfYear],[DayOfMonth],[DayOfWeek],[DayNameOfWeek],[Daytime])
Values(@Year,@Date,@SemiYear,@Quarter,@month,
@DNOfYear,@DNOfMonth,@DNOfWeek,@DNameOfWeek,@Daytime)
--Select @Year ,@Date ,@SemiYear ,@Quarter ,@month, @DNOfYear ,@DNOfMonth ,@DNOfWeek , @DNameOfWeek, @Daytime
Select @oDate = DateAdd(hh,1,@oDate)
End
iceno,goods,date,num我要查找数据表中的同一天的数据(date)且按相同的iceno号在后面按时间增加一列,如:2006110901,后面两位为区分不同iceno号所生成的不同序号。
如:
1000,xx,2006-11-9,1,2006110901
1000,yy,2006-11-9,1,2006110901
1000,zz2006-11-9,2,2006110901
1001,xx,2006-11-9,1,2006110902
1001,hh,2006-11-9,2,2006110902
1002,hh,2006-11-9,1,2006110903
1002,xx,2006-11-9,1,2006110903
请各位帮忙!谢谢了