ERP
sql sever
tools 工具
第一步：创建农历日期函数CODE:USE [AIO7_COST_FC]GO/****** Object: UserDefinedFunction [dbo].[fn_GetLunar] Script Date: 07/06/2015 17:23:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[fn_GetLunar](@solarDay DATETIME) RETURNS datetime AS BEGIN DECLARE @solData int DECLARE @offset int DECLARE @iLunar int DECLARE @i INT DECLARE @j INT DECLARE @yDays int DECLARE @mDays int DECLARE @mLeap int DECLARE @mLeapNum int DECLARE @bLeap smallint DECLARE @temp int DECLARE @YEAR INT DECLARE @MONTH INT DECLARE @DAY INT DECLARE @OUTPUTDATE DATETIME --保证传进来的日期是不带时间 SET @solarDay=cast(@solarDay AS char(10)) SET @offset=CAST(@solarDay-'1900-01-30' AS INT) --确定农历年开始 SET @i=1900 --SET @offset=@solData WHILE @i<2050 AND @offset>0 BEGIN SET @yDays=348 SET @mLeapNum=0 SELECT @iLunar=dataInt FROM SolarData WHERE yearId=@i --传回农历年的总天数 SET @j=32768 WHILE @j>8 BEGIN IF @iLunar & @j >0 SET @yDays=@yDays+1 SET @j=@j/2 END --传回农历年闰哪个月 1-12 , 没闰传回 0 SET @mLeap = @iLunar & 15 --传回农历年闰月的天数 ,加在年的总天数上 IF @mLeap > 0 BEGIN IF @iLunar & 65536 > 0 SET @mLeapNum=30 ELSE SET @mLeapNum=29 SET @yDays=@yDays+@mLeapNum END SET @offset=@offset-@yDays SET @i=@i+1 END IF @offset <= 0 BEGIN SET @offset=@offset+@yDays SET @i=@i-1 END --确定农历年结束 SET @YEAR=@i --确定农历月开始 SET @i = 1 SELECT @iLunar=dataInt FROM SolarData WHERE yearId=@YEAR --判断那个月是润月 SET @mLeap = @iLunar & 15 SET @bLeap = 0 WHILE @i < 13 AND @offset > 0 BEGIN --判断润月 SET @mDays=0 IF (@mLeap > 0 AND @i = (@mLeap+1) AND @bLeap=0) BEGIN--是润月 SET @i=@i-1 SET @bLeap=1 --传回农历年闰月的天数 IF @iLunar & 65536 > 0 SET @mDays = 30 ELSE SET @mDays = 29 END ELSE --不是润月 BEGIN SET @j=1 SET @temp = 65536 WHILE @j<=@i BEGIN SET @temp=@temp/2 SET @j=@j+1 END IF @iLunar & @temp > 0 SET @mDays = 30 ELSE SET @mDays = 29 END --解除闰月 IF @bLeap=1 AND @i= (@mLeap+1) SET @bLeap=0 SET @offset=@offset-@mDays SET @i=@i+1 END IF @offset <= 0 BEGIN SET @offset=@offset+@mDays SET @i=@i-1 END --确定农历月结束 SET @MONTH=@i --确定农历日结束 SET @DAY=@offset SET @OUTPUTDATE=CAST(@Year AS char(4)) + RIGHT('0' + CAST(@Month AS varchar(2)), 2) + RIGHT('0' + CAST(@Day AS varchar(2)), 2) RETURN @OUTPUTDATE ENDGO
第二步：生成公历/农历对照表CODE:select convert(char(10),dateadd(d,number,'2015-1-1'),23) as GL,--公历 dbo.fn_GetLunar(dateadd(d,number,'2015-1-1')) as NL --农历INTO LunarCalenderContrastTable --公历农历对照表from master..spt_values where type='p'
第三步：根据身份证提取员工公历生日，并转化为农历生日以及今年农历生日对应的公历，此查询的列是为滚动字幕准备CODE:Select null, 'O',EmpName+'，在你生日来临之际，祝你生日快乐，健康幸福！',Convert(varchar(10), Getdate(), 120), Convert(varchar(10), cast(T1.GL as DATETIME)+3, 120),'系统',Cast(SubString(IDCard,7,8) as DateTime) AS '身份证生日'--,DateDiff(Year,Cast(SubString(IDCard,7,8) as DateTime),GetDate())AS '年龄', dbo.fn_GetLunar1(Cast(SubString(IDCard,7,8) as DateTime)) AS '农历出生日期',--DateAdd(Year,DateDiff(Year,Cast(SubString(IDCard,7,8) as DateTime),GetDate()),Cast(SubString(IDCard,7,8) as DateTime)) as '本年阳历生日',Cast(Cast(SubString(dbo.fn_GetLunar1(Cast(SubString(IDCard,7,8) as DateTime)),1,4) AS INT)+Cast(DateDiff(Year,Cast(SubString(IDCard,7,8) as DateTime),GetDate()) AS INT) AS NVARCHAR)+Cast(SubString(dbo.fn_GetLunar1(Cast(SubString(IDCard,7,8) as DateTime)),5,4) AS NVARCHAR) AS '今年农历生日',T1.GL as '对应的阳历'from LunarCalenderContrastTable T1LEFT JOIN HREmp T ON Cast(cast(SubString(dbo.fn_GetLunar1(Cast(SubString(IDCard,7,8) as DateTime)),1,4) AS INT)+Cast(DateDiff(Year,Cast(SubString(IDCard,7,8) as DateTime),GetDate()) AS INT) AS NVARCHAR)+Cast(SubString(dbo.fn_GetLunar1(Cast(SubString(IDCard,7,8) as DateTime)),5,4) AS NVARCHAR)=T1.NLWhere Cast(T1.GL AS DATETIME)>GETDATE() and Cast(T1.GL AS DATETIME)< GETDATE()+8 and T.EmpType = 'O' and T.IsClose = 'N'
第四步：滚动字幕插入的一些设置自行根据企业实际需求进行相关设置。