在實施北方企業的時候，有不少企業都提出他們那邊過生日基本是根據農曆來，花了些心思幫客戶做出來了，公司可以將農曆生日提醒也內建到資料庫中，以方便其他ERP實施員直接使用。以下是製作過程。
工具/原料
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 @[email protected] WHILE @i<2050 AND @offset>0 BEGIN SET @yDays=348 SET @mLeapNum=0 SELECT @iLunar=dataInt FROM SolarData WHERE [email protected] --傳回農曆年的總天數 SET @j=32768 WHILE @j>8 BEGIN IF @iLunar & @j >0 SET @[email protected]+1 SET @[email protected]/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 @yD[email protected][email protected] END SET @[email protected]@yDays SET @[email protected]+1 END IF @offset <= 0 BEGIN SET @[email protected][email protected] SET @[email protected] END --確定農曆年結束 SET @[email protected] --確定農曆月開始 SET @i = 1 SELECT @iLunar=dataInt FROM SolarData WHERE [email protected] --判斷那個月是潤月 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 @[email protected] 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<[email protected] BEGIN SET @[email protected]/2 SET @[email protected]+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 @[email protected]@mDays SET @[email protected]+1 END IF @offset <= 0 BEGIN SET @[email protected][email protected] SET @[email protected] END --確定農曆月結束 SET @[email protected] --確定農曆日結束 SET @[email protected] 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'
第四步：滾動字幕插入的一些設定
自行根據企業實際需求進行相關設定。