SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[EightCharactersDate]--金宝 编码
(@sDate nvarchar(50))
AS
BEGIN
declare @dstandardTime varchar(50);--标准时间 yyyy-mm-dd hh:mm:ss
declare @iYear int;--年
declare @iMonth int;--月
declare @iDay int;--日
declare @iHour int;--时
declare @iYearTG int;--年干数
declare @iYearDZ int;--年地支
declare @iMonthTG int;--月干数
declare @iMonthDZ int;--月地支
declare @iDayJS int;--日基数
declare @iDayTG int;--日干数
declare @iDayDZ int;--日地支
declare @iHourTG int;--时干数
declare @iHourDZ int;--时地支
declare @sRet varchar(50);--计算结果
--declare @sRet varchar(50);
BEGIN TRY
set @sRet=‘‘;--初始化
if (isdate(@sDate)!=1)
begin
select 0 as iRet,N‘非日期格式‘ as sRet;
return;
end;
create table #s_dict(ikey int,sValue nvarchar(50),sType nvarchar(50));
insert #s_dict(ikey,sValue,sType)
select 1 as ikey,N‘甲‘ as sValue,N‘天干‘ as sType union
select 2 as ikey,N‘乙‘ as sValue,N‘天干‘ as sType union
select 3 as ikey,N‘丙‘ as sValue,N‘天干‘ as sType union
select 4 as ikey,N‘丁‘ as sValue,N‘天干‘ as sType union
select 5 as ikey,N‘戊‘ as sValue,N‘天干‘ as sType union
select 6 as ikey,N‘己‘ as sValue,N‘天干‘ as sType union
select 7 as ikey,N‘庚‘ as sValue,N‘天干‘ as sType union
select 8 as ikey,N‘辛‘ as sValue,N‘天干‘ as sType union
select 9 as ikey,N‘壬‘ as sValue,N‘天干‘ as sType union
select 10 as ikey,N‘癸‘ as sValue,N‘天干‘ as sType union
select 1 as ikey,N‘子‘ as sValue,N‘地支‘ as sType union
select 2 as ikey,N‘丑‘ as sValue,N‘地支‘ as sType union
select 3 as ikey,N‘寅‘ as sValue,N‘地支‘ as sType union
select 4 as ikey,N‘卯‘ as sValue,N‘地支‘ as sType union
select 5 as ikey,N‘辰‘ as sValue,N‘地支‘ as sType union
select 6 as ikey,N‘巳‘ as sValue,N‘地支‘ as sType union
select 7 as ikey,N‘午‘ as sValue,N‘地支‘ as sType union
select 8 as ikey,N‘未‘ as sValue,N‘地支‘ as sType union
select 9 as ikey,N‘申‘ as sValue,N‘地支‘ as sType union
select 10 as ikey,N‘酉‘ as sValue,N‘地支‘ as sType union
select 11 as ikey,N‘戌‘ as sValue,N‘地支‘ as sType union
select 12 as ikey,N‘亥‘ as sValue,N‘地支‘ as sType;
--时间拆解
select @dstandardTime=CONVERT(varchar(19),cast(@sDate as datetime), 120)
select @iYear=cast(substring(@dstandardTime,1,4) as int);
select @iMonth=cast(substring(@dstandardTime,6,2) as int);
select @iDay=cast(substring(@dstandardTime,9,2) as int);
select @iHour=cast(substring(@dstandardTime,12,2) as int);
--年天干:（年份-3）÷10取余数，对应十天干的顺序即为所求的天干
set @iYearTG=(@iYear-3) % 10;
if @iYearTG=0
begin
set @iYearTG=10;
end;
select @sRet=sValue from #s_dict where ikey=@iYearTG and sType=‘天干‘;
--年地支:（年份-3）÷12取余数，对应十二地支的顺序即为所求的地支
set @iYearDZ=(@iYear-3) % 12;
if @iYearDZ=0
begin
set @iYearDZ=12;
end;
select @sRet=@sRet+sValue from #s_dict where ikey=@iYearDZ and sType=‘地支‘;
--月天干:年干数*2+月份 除10 求余
set @iMonthTG=(@iYearTG*2+@iMonth)%10;
if @iMonthTG=0
begin
set @iMonthTG=10;
end;
select @sRet=@sRet+sValue from #s_dict where ikey=@iMonthTG and sType=‘天干‘;
--月地支
set @iMonthDZ=@iMonth+2;--因为 从寅开始的1月寅
if @iMonthDZ>12
begin
set @iMonthDZ=@iMonthTG-12;
end;
select @sRet=@sRet+sValue from #s_dict where ikey=@iMonthDZ and sType=‘地支‘;
--日基数
if @iYear>=2000
begin
set @iDayJS=((((@iYear%100)+7)*5+15+((@iYear%100)+19)/4)+datediff(DY,cast(@iYear as varchar(4))+‘-01-01‘,@sDate)+1)%60;
end;
--日天干
set @iDayTG=@iDayJS%10;
if @iDayTG=0
begin
set @iDayTG=10;
end;
select @sRet=@sRet+sValue from #s_dict where ikey=@iDayTG and sType=‘天干‘;
--日地支
set @iDayDZ=@iDayJS%12;
if @iDayDZ=0
begin
set @iDayDZ=12;
end;
select @sRet=@sRet+sValue from #s_dict where ikey=@iDayDZ and sType=‘地支‘;
--时支 时支公式：时支=小时÷2-1（小时为偶数），时支=（小时+1）÷2-1（小时为奇数）
if (@iHour%2)=0
begin
set @iHourDZ=@iHour/2-1;
end
else
begin
set @iHourDZ=(@iHour+1)/2-1;
end;
--时干 时干=日干×2+时支（晨子=-1，夜子=11）
set @iHourTG=(@iDayTG*2+@iHourDZ)%10;
if @iHourTG=0
begin
set @iHourTG=10;
end;
--时干
--时地支
set @iHourDZ=@iHourDZ+2;--因为 从寅开始
if @iHourDZ>12
begin
set @iHourDZ=@iHourDZ-12;
end;
select @sRet=@sRet+sValue from #s_dict where ikey=@iHourTG and sType=‘天干‘;
select @sRet=@sRet+sValue from #s_dict where ikey=@iHourDZ and sType=‘地支‘;
select 1 as iRet,@sRet as sRet;
END TRY
BEGIN CATCH
select 0 as iRet,ERROR_MESSAGE() as sRet;
END CATCH
END;
原文：https://www.cnblogs.com/530263009QQ/p/SQL.html