自动生成编码、日期；随机生成时间

最新推荐文章于 2021-05-24 12:26:53 发布

转载最新推荐文章于 2021-05-24 12:26:53 发布 · 457 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/songjiali/archive/2011/11/14/2248702.html

文章标签：

#python

本文介绍如何使用SQL自动生成编码、日期和随机生成时间的功能，并提供了具体代码示例。主要内容包括自动生成唯一标识符、日期编码以及通过游标实现随机时间生成。

自动生成编码：

1 select 'SSZ'+(RIGHT(10000000001 + ISNULL(RIGHT(MAX(MaterialCode),4),0),4)) as A from H6_MaterialFab where MaterialCode like 'SSZ%'

自动生成日期：

SELECT 'CFR'+
DATENAME(YY, getdate()) + 
DATENAME(MM, GETDATE()) + 
(RIGHT(10000000001 + ISNULL(RIGHT(MAX(BillCode),3),0),3)) as A 
FROM H6_StockMaterialAccBill  
where DATENAME(YY, getdate()) + DATENAME(MM, getdate()) = substring(BillCode, 7, 6)

SELECT 'FMD' + 
'GLY' 
+ convert(char(8),GETDATE(),112) 
--+ DATENAME(YY,getdate())
-- + DATENAME(MM,GETDATE())
 + (RIGHT(10000000001 + ISNULL(MAX(RIGHT(Code,3)),0),3)) 
 as A FROM Financial_PayApplyFabBill  
 --where DATENAME(YY, getdate()) + DATENAME(MM, getdate()) = substring(Code, 7, 6)

随机生成时间：（游标）

 declare cur cursor read_only for select PersonnelCode from KA_Personnel

declare @PersonnelCode varchar(40)  o

pen cur  fetch next from cur into @PersonnelCode  while (@@fetch_status =0)

begin insert into KA_Result([PersonnelCode] ,[ResultDateTime],[MorningDateTime1],[MorningDateTime2],[AfternoonDateTime1] ,[AfternoonDateTime2] ,[EveningDateTime1] ,[EveningDateTime2])

select @PersonnelCode, '{0}', CONVERT(time(0),dateadd(Second,Convert(int,CEILING(RAND()*1200)),'07:25:00')),

CONVERT(time(0),dateadd(Second,Convert(int,CEILING(RAND()*1200)),'11:25:00')),

CONVERT(time(0),dateadd(Second,Convert(int,CEILING(RAND()*1200)),'12:55:00')),

CONVERT(time(0),dateadd(Second,Convert(int,RAND()*1200),'16:55:00')),

CONVERT(time(0),dateadd(Second,Convert(int,RAND()*1200),'17:55:00')),

CONVERT(time(0),dateadd(Second,Convert(int,RAND()*1200),'20:55:00'))

 fetch next from cur into @PersonnelCode end close cur deallocate cur

转载于:https://www.cnblogs.com/songjiali/archive/2011/11/14/2248702.html