sql陽曆轉陰曆

关注

转载最新推荐文章于 2026-06-04 09:26:30 发布 660 阅读 AI 写同款· GEO 优化 ›

sql中农历阳历之间的转换问题 本文来自tony的完善版。链接地址为： http://topic.youkuaiyun.com/u/20100424/07/29529d9d-bc54-4877-b198-4426b4d85024.html 呵呵，由于需要做个农历生日提醒的所以找到了，保留着，收藏为网摘的实在是有些乱，还是放这比较好，O(∩_∩)O哈哈~ --创建基础数据表 if object_id('SolarData') is not null 阅读详情

USE [rossic]
GO
/****** Object: StoredProcedure [dbo].[fn_GetLunar] Script Date: 02/23/2010 15:46:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[fn_GetLunar]
-- RETURNS datetime
@solarDay DATETIME ,
@OUTPUTDATE DATETIME output
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 @solarDay datetime



 --保证传进来的日期是不带时间
 SET @solarDay = CONVERT(varchar(10),@solarDay,120)
-- getdate()
 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((CAST(@YEAR AS VARCHAR(4))+'-'+CAST(@MONTH AS VARCHAR(2))+'-'+CAST(@DAY AS VARCHAR(2))) AS DATETIME)

END