巧用SQL函數(shù)讓公歷變農(nóng)歷
如果使用SQL函數(shù)讓公歷轉(zhuǎn)農(nóng)歷，應該如何寫這個函數(shù)呢？下面就將為您介紹讓公歷變農(nóng)歷的SQL函數(shù)寫法，供您參考，希望對您學習SQL有所幫助。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetLunar1](
@solarDay DATETIME)
RETURNS varchar(200)
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 varchar(100)
--保證傳進來的日期是不帶時間
SET @solarDay=cast(@solarDay AS char(10))
SET @offset=CAST(@solarDay-'1900-01-30' AS INT)
--確定農(nóng)歷年開始
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
--傳回農(nóng)歷年的總天數(shù)
SET @j=32768
WHILE @j>8
BEGIN
IF @iLunar & @j >0
SET @yDays=@yDays+1
SET @j=@j/2
END
--傳回農(nóng)歷年閏哪個月 1-12 , 沒閏傳回 0
SET @mLeap = @iLunar & 15
--傳回農(nóng)歷年閏月的天數(shù) ,加在年的總天數(shù)上
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
--確定農(nóng)歷年結(jié)束
SET @YEAR=@i
--確定農(nóng)歷月開始
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
--傳回農(nóng)歷年閏月的天數(shù)
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
--確定農(nóng)歷月結(jié)束
SET @MONTH=@i
--確定農(nóng)歷日結(jié)束
SET @DAY=@offset
if @bLeap=1
SET @OUTPUTDATE=convert(varchar(10),CAST((CAST(@YEAR AS VARCHAR(4))+'-'+CAST(@MONTH AS VARCHAR(2))+'-'+CAST(@DAY AS VARCHAR(2))) AS DATETIME) ,120)+',1'
else
SET @OUTPUTDATE=convert(varchar(10),CAST((CAST(@YEAR AS VARCHAR(4))+'-'+CAST(@MONTH AS VARCHAR(2))+'-'+CAST(@DAY AS VARCHAR(2))) AS DATETIME) ,120)+',0'
RETURN @OUTPUTDATE
END
【編輯推薦】
字符型IP地址轉(zhuǎn)換成數(shù)字IP的SQL函數(shù)
