/*------------------------------------------------------------------------------- 功能: 返回指定日期的农历日期 (需配合 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