/*-------------------------------------------------------------------------------
功能:
返回指定日期的农历日期
(需配合 sys_date_lunar 表使用)
参数说明:
@solarDay 日期
@type 日期: 0: 年月日
文本: 1: 月日的文本
数字: 20: ymd , 21 md
例:
select dbo.usf_GetDateLunar('2023-12-01',0) 返回: 2023-10-19
select dbo.usf_GetDateLunar('2023-12-01',1) 返回: 十月十九
select dbo.usf_GetDateLunar('2023-12-01',20) 返回: 20231019
select dbo.usf_GetDateLunar('2023-12-01',21) 返回: 1019
-------------------------------------------------------------------------------------*/
create FUNCTION [dbo].[usf_GetDateLunar](
@solarDay as DATETIME , @type as tinyint = 0)
/* @type 返回格式
*/
RETURNS varchar(200)
AS
BEGIN
--转自CSDN
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 varchar(100)
--保证传进来的日期是不带时间
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 sys_date_lunar 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 sys_date_lunar 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
/*
if @bLeap=1
SET @OUTPUTDATE=(CAST(@YEAR AS VARCHAR(4))+'-闰'+CAST(@MONTH AS VARCHAR(2))+'-'+CAST(@DAY AS VARCHAR(2)))
else
SET @OUTPUTDATE=(CAST(@YEAR AS VARCHAR(4))+'-'+CAST(@MONTH AS VARCHAR(2))+'-'+CAST(@DAY AS VARCHAR(2)))
*/
SET @OUTPUTDATE=
case @type
when 1 then
case when @bLeap=1 then '闰' else '' end
+ case @MONTH when 1 then '正月' when 2 then '二月' when 3 then '三月' when 4 then '四月' when 5 then '五月' when 6 then '六月' when 7 then '七月'
when 8 then '八月' when 9 then '九月' when 10 then '十月' when 11 then '十一月' when 12 then '腊月' else '出错' end
+ case @DAY when 1 then '初一' when 2 then '初二'when 3 then '初三'when 4 then '初四'when 5 then '初五'
when 6 then '初六' when 7 then '初七'when 8 then '初八'when 9 then '初九'when 10 then '初十'
when 11 then '十一' when 12 then '十二'when 13 then '十三'when 14 then '十四'when 15 then '十五'
when 16 then '十六' when 17 then '十七'when 18 then '十八'when 19 then '十九'when 20 then '廿十'
when 21 then '廿一' when 22 then '廿二'when 23 then '廿三'when 24 then '廿四'when 25 then '廿五'
when 26 then '廿六' when 27 then '廿七'when 28 then '廿八'when 29 then '廿九'when 30 then '三十'
else '出错' end
when 20 then CONVERT(varchar(10), @YEAR *10000 + @MONTH*100 +@DAY)
when 21 then CONVERT(varchar(10), @MONTH*100 +@DAY)
else convert(varchar(10), convert(datetime, (CAST(@YEAR AS VARCHAR(4))+'-'+ CAST(@MONTH AS VARCHAR(2))+'-' + CAST(@DAY AS VARCHAR(2))) ),120)
end
RETURN @OUTPUTDATE
END
GO
标签：公历,yDays,SET,--,offset,int,日期,SQL,DECLARE From： https://www.cnblogs.com/easybi/p/17887018.html