Question

SQL 语句 生成当前时间 年月日小时分钟 再加4个随机数的字符串。

比如：201103070915加4个随机数2315 结果为2011030709152315
DECLARE @rand 。。。。。。
SET @rand 。。。。。。。。。。。。。
结果要为narvchar字符串

我今天都在线

Answer 1

--测试环境sql2005 2005以下版本可能存在问题
--在自定义函数中不能使用日期函数getdate(),所以这里要建一个视图用来返回当前时间
go
if object_id('viewNow') is not null
begin
drop view viewNow
end
go
Create View viewNow
as
select getdate() as now

--在自定义函数中不能使用函数rand()所以这里要建立一个视图来返回4位随机数字
go
if object_id('viewRandNumber') is not null
begin
drop view viewRandNumber
end
go
Create View viewRandNumber
as
select CEILING(RAND()*10000) as RandNumber

--建立自定义函数
go
if object_id('GenerateCode') is not null
begin
drop function GenerateCode
end
go
create function GenerateCode()
returns varchar(16)
as
begin
declare @now datetime
select @now=now from viewNow
declare @nowVarchar varchar(12)
set @nowVarchar=convert(varchar(8),@now,112)+replace(convert(varchar(8),@now,108),':','')
declare @RandNumber int
select @RandNumber=RandNumber from viewRandNumber
return @nowVarchar+convert(varchar(4),@RandNumber)
end

--测试结果
select dbo.GenerateCode() as code

--code
2011030710478029

Answer 2

DECLARE @rand NVARCHAR(16)

SET @rand = REPLACE(CONVERT(NVARCHAR(16), GETDATE(), 120), '-', '');
SET @rand = REPLACE(@rand, ' ', '');
SET @rand = REPLACE(@rand, ':', '');
SET @rand = @rand + CAST(FLOOR ((RAND() * 900 + 100)) AS NVARCHAR(3));

PRINT @rand

Answer 3

select convert(char(19),getdate(),112)

Answer 4

declare @autoNo as nvarchar(15)
set @autoNo = CONVERT(nvarchar(10), GETDATE(),112) +cast(DATENAME(HOUR,GETDATE()) as nvarchar)+ cast(DATENAME(MINUTE,GETDATE()) as nvarchar) + right('0000' + CAST( cast(rand()*10000 as int) as nvarchar),4)
print @autoNo