ALTER FUNCTION [dbo].[f_SysGetLunar]( @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) --确定农历年开始 SET @i=1900 --SET @offset=@solData WHILE @i<2050 AND @offset>0 BEGIN SET @yDays=348 SET @mLeapNum=0 SELECT @iLunar=dataInt FROM Sysc80 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 Sysc80 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))) RETURN @OUTPUTDATE END --******************************************************** --Sysc80表内容 select * from Sysc80 yearId databin dataInt 1900 19416 19416 1901 19168 19168 1902 42352 42352 1903 21717 21717 1904 53856 53856 1905 55632 55632 1906 91476 91476 1907 22176 22176 1908 39632 39632 1909 21970 21970 1910 19168 19168 1911 42422 42422 1912 42192 42192 1913 53840 53840 1914 119381 119381 1915 46400 46400 1916 54944 54944 1917 44450 44450 1918 38320 38320 1919 84343 84343 1920 18800 18800 1921 42160 42160 1922 46261 46261 1923 27216 27216 1924 27968 27968 1925 109396 109396 1926 11104 11104 1927 38256 38256 1928 21234 21234 1929 18800 18800 1930 25958 25958 1931 54432 54432 1932 59984 59984 1933 28309 28309 1934 23248 23248 1935 11104 11104 1936 100067 100067 1937 37600 37600 1938 116951 116951 1939 51536 51536 1940 54432 54432 1941 120998 120998 1942 46416 46416 1943 22176 22176 1944 107956 107956 1945 9680 9680 1946 37584 37584 1947 53938 53938 1948 43344 43344 1949 46423 46423 1950 27808 27808 1951 46416 46416 1952 86869 86869 1953 19872 19872 1954 42448 42448 1955 83315 83315 1956 21200 21200 1957 43432 43432 1958 59728 59728 1959 27296 27296 1960 44710 44710 1961 43856 43856 1962 19296 19296 1963 43748 43748 1964 42352 42352 1965 21088 21088 1966 62051 62051 1967 55632 55632 1968 23383 23383 1969 22176 22176 1970 38608 38608 1971 19925 19925 1972 19152 19152 1973 42192 42192 1974 54484 54484 1975 53840 53840 1976 54616 54616 1977 46400 46400 1978 46496 46496 1979 103846 103846 1980 38320 38320 1981 18864 18864 1982 43380 43380 1983 42160 42160 1984 45690 45690 1985 27216 27216 1986 27968 27968 1987 44870 44870 1988 43872 43872 1989 38256 38256 1990 19189 19189 1991 18800 18800 1992 25776 25776 1993 29859 29859 1994 59984 59984 1995 27480 27480 1996 21952 21952 1997 43872 43872 1998 38613 38613 1999 37600 37600 2000 51552 51552 2001 55636 55636 2002 54432 54432 2003 55888 55888 2004 30034 30034 2005 22176 22176 2006 43959 43959 2007 9680 9680 2008 37584 37584 2009 51893 51893 2010 43344 43344 2011 46240 46240 2012 47780 47780 2013 44368 44368 2014 21977 21977 2015 19360 19360 2016 42416 42416 2017 86390 86390 2018 21168 21168 2019 43312 43312 2020 31060 31060 2021 27296 27296 2022 44368 44368 2023 23378 23378 2024 19296 19296 2025 42726 42726 2026 42208 42208 2027 53856 53856 2028 60005 60005 2029 54576 54576 2030 23200 23200 2031 30371 30371 2032 38608 38608 2033 19415 19415 2034 19152 19152 2035 42192 42192 2036 118966 118966 2037 53840 53840 2038 54560 54560 2039 56645 56645 2040 46496 46496 2041 22224 22224 2042 21938 21938 2043 18864 18864 2044 42359 42359 2045 42160 42160 2046 43600 43600 2047 111189 111189 2048 27936 27936 2049 44448 44448
SQLServer公历转农历函数(1900年2049年)
版权所有：jory—经得起折磨，耐得住寂寞