数据库中旧历转新历

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER         PROCEDURE p_OldtoNewDate @pdDate as datetime,@pbLeapMonth as bit ,@return as datetime output
AS
set nocount on
/*
declare @pdDate as datetime,@pbLeapMonth as bit
select @pdDate=Getdate(),@pbLeapMonth=0
*/
--declare @return as datetime

--合法性：1900~2100年之间
If Year(@pdDate) < 1900 Or Year(@pdDate) > 2100
    select @return=null
else
begin

  --1990~2100年之间的二进制编码
  --前12位对应每个月，1为30天，0为29天
  --后4位为润月的月份，"0000"为当年没有润月，"1111"为上一年的润月是30天，否则为29天
  --编码总计长度为16，不足则前面以0补足
  --新增年份可以添加到数组后面
 select * into #tmplunarInfo from (
 select 1900 as nYear,'0100101111011000' as sCode union all select 1901 as nYear,'0100101011100000' as sCode union all select 1902 as nYear,'1010010101110000' as sCode union all select 1903 as nYear,'0101010011010101' as sCode union all select 1904 as nYear,'1101001001100000' as sCode union all select 1905 as nYear,'1101100101010000' as sCode union all select 1906 as nYear,'0101010101010100' as sCode union all select 1907 as nYear,'0101011010101111' as sCode union all select 1908 as nYear,'1001101011010000' as sCode union all select 1909 as nYear,'0101010111010010' as sCode
 union all
 select 1910 as nYear,'0100101011100000' as sCode union all select 1911 as nYear,'1010010110110110' as sCode union all select 1912 as nYear,'1010010011010000' as sCode union all select 1913 as nYear,'1101001001010000' as sCode union all select 1914 as nYear,'1101001001010101' as sCode union all select 1915 as nYear,'1011010101001111' as sCode union all select 1916 as nYear,'1101011010100000' as sCode union all select 1917 as nYear,'1010110110100010' as sCode union all select 1918 as nYear,'1001010110110000' as sCode union all select 1919 as nYear,'0100100101110111' as sCode
 union all
 select 1920 as nYear,'0100100101111111' as sCode union all select 1921 as nYear,'1010010010110000' as sCode union all select 1922 as nYear,'1011010010110101' as sCode union all select 1923 as nYear,'0110101001010000' as sCode union all select 1924 as nYear,'0110110101000000' as sCode union all select 1925 as nYear,'1010101101010100' as sCode union all select 1926 as nYear,'0010101101101111' as sCode union all select 1927 as nYear,'1001010101110000' as sCode union all select 1928 as nYear,'0101001011110010' as sCode union all select 1929 as nYear,'0100100101110000' as sCode
 union all
 select 1930 as nYear,'0110010101100110' as sCode union all select 1931 as nYear,'1101010010100000' as sCode union all select 1932 as nYear,'1110101001010000' as sCode union all select 1933 as nYear,'0110101010010101' as sCode union all select 1934 as nYear,'0101101011011111' as sCode union all select 1935 as nYear,'0010101101100000' as sCode union all select 1936 as nYear,'1000011011100011' as sCode union all select 1937 as nYear,'1001001011101111' as sCode union all select 1938 as nYear,'1100100011010111' as sCode union all select 1939 as nYear,'1100100101011111' as sCode
 union all
 select 1940 as nYear,'1101010010100000' as sCode union all select 1941 as nYear,'1101100010100110' as sCode union all select 1942 as nYear,'1011010101011111' as sCode union all select 1943 as nYear,'0101011010100000' as sCode union all select 1944 as nYear,'1010010110110100' as sCode union all select 1945 as nYear,'0010010111011111' as sCode union all select 1946 as nYear,'1001001011010000' as sCode union all select 1947 as nYear,'1101001010110010' as sCode union all select 1948 as nYear,'1010100101010000' as sCode union all select 1949 as nYear,'1011010101010111' as sCode
 union all
 select 1950 as nYear,'0110110010100000' as sCode union all select 1951 as nYear,'1011010101010000' as sCode union all select 1952 as nYear,'0101001101010101' as sCode union all select 1953 as nYear,'0100110110101111' as sCode union all select 1954 as nYear,'1010010110110000' as sCode union all select 1955 as nYear,'0100010101110011' as sCode union all select 1956 as nYear,'0101001010111111' as sCode union all select 1957 as nYear,'1010100110101000' as sCode union all select 1958 as nYear,'1110100101010000' as sCode union all select 1959 as nYear,'0110101010100000' as sCode
 union all
 select 1960 as nYear,'1010111010100110' as sCode union all select 1961 as nYear,'1010101101010000' as sCode union all select 1962 as nYear,'0100101101100000' as sCode union all select 1963 as nYear,'1010101011100100' as sCode union all select 1964 as nYear,'1010010101110000' as sCode union all select 1965 as nYear,'0101001001100000' as sCode union all select 1966 as nYear,'1111001001100011' as sCode union all select 1967 as nYear,'1101100101010000' as sCode union all select 1968 as nYear,'0101101101010111' as sCode union all select 1969 as nYear,'0101011010100000' as sCode
 union all
 select 1970 as nYear,'1001011011010000' as sCode union all select 1971 as nYear,'0100110111010101' as sCode union all select 1972 as nYear,'0100101011010000' as sCode union all select 1973 as nYear,'1010010011010000' as sCode union all select 1974 as nYear,'1101010011010100' as sCode union all select 1975 as nYear,'1101001001010000' as sCode union all select 1976 as nYear,'1101010101011000' as sCode union all select 1977 as nYear,'1011010101000000' as sCode union all select 1978 as nYear,'1011011010100000' as sCode union all select 1979 as nYear,'1001010110100110' as sCode
 union all
 select 1980 as nYear,'1001010110111111' as sCode union all select 1981 as nYear,'0100100110110000' as sCode union all select 1982 as nYear,'1010100101110100' as sCode union all select 1983 as nYear,'1010010010110000' as sCode union all select 1984 as nYear,'1011001001111010' as sCode union all select 1985 as nYear,'0110101001010000' as sCode union all select 1986 as nYear,'0110110101000000' as sCode union all select 1987 as nYear,'1010111101000110' as sCode union all select 1988 as nYear,'1010101101100000' as sCode union all select 1989 as nYear,'1001010101110000' as sCode
 union all
 select 1990 as nYear,'0100101011110101' as sCode union all select 1991 as nYear,'0100100101110000' as sCode union all select 1992 as nYear,'0110010010110000' as sCode union all select 1993 as nYear,'0111010010100011' as sCode union all select 1994 as nYear,'1110101001010000' as sCode union all select 1995 as nYear,'0110101101011000' as sCode union all select 1996 as nYear,'0101101011000000' as sCode union all select 1997 as nYear,'1010101101100000' as sCode union all select 1998 as nYear,'1001011011010101' as sCode union all select 1999 as nYear,'1001001011100000' as sCode
 union all
 select 2000 as nYear,'1100100101100000' as sCode union all select 2001 as nYear,'1101100101010100' as sCode union all select 2002 as nYear,'1101010010100000' as sCode union all select 2003 as nYear,'1101101001010000' as sCode union all select 2004 as nYear,'0111010101010010' as sCode union all select 2005 as nYear,'0101011010100000' as sCode union all select 2006 as nYear,'1010101110110111' as sCode union all select 2007 as nYear,'0010010111010000' as sCode union all select 2008 as nYear,'1001001011010000' as sCode union all select 2009 as nYear,'1100101010110101' as sCode
 union all
 select 2010 as nYear,'1010100101010000' as sCode union all select 2011 as nYear,'1011010010100000' as sCode union all select 2012 as nYear,'1011101010100100' as sCode union all select 2013 as nYear,'1010110101010000' as sCode union all select 2014 as nYear,'0101010111011001' as sCode union all select 2015 as nYear,'0100101110100000' as sCode union all select 2016 as nYear,'1010010110110000' as sCode union all select 2017 as nYear,'0101000101110110' as sCode union all select 2018 as nYear,'0101001010111111' as sCode union all select 2019 as nYear,'1010100100110000' as sCode
 union all
 select 2020 as nYear,'0111100101010100' as sCode union all select 2021 as nYear,'0110101010100000' as sCode union all select 2022 as nYear,'1010110101010000' as sCode union all select 2023 as nYear,'0101101101010010' as sCode union all select 2024 as nYear,'0100101101100000' as sCode union all select 2025 as nYear,'1010011011100110' as sCode union all select 2026 as nYear,'1010010011100000' as sCode union all select 2027 as nYear,'1101001001100000' as sCode union all select 2028 as nYear,'1110101001100101' as sCode union all select 2029 as nYear,'1101010100110000' as sCode
 union all
 select 2030 as nYear,'0101101010100000' as sCode union all select 2031 as nYear,'0111011010100011' as sCode union all select 2032 as nYear,'1001011011010000' as sCode union all select 2033 as nYear,'0100101011111011' as sCode union all select 2034 as nYear,'0100101011010000' as sCode union all select 2035 as nYear,'1010010011010000' as sCode union all select 2036 as nYear,'1101000010110110' as sCode union all select 2037 as nYear,'1101001001011111' as sCode union all select 2038 as nYear,'1101010100100000' as sCode union all select 2039 as nYear,'1101110101000101' as sCode
 union all
 select 2040 as nYear,'1011010110100000' as sCode union all select 2041 as nYear,'0101011011010000' as sCode union all select 2042 as nYear,'0101010110110010' as sCode union all select 2043 as nYear,'0100100110110000' as sCode union all select 2044 as nYear,'1010010101110111' as sCode union all select 2045 as nYear,'1010010010110000' as sCode union all select 2046 as nYear,'1010101001010000' as sCode union all select 2047 as nYear,'1011001001010101' as sCode union all select 2048 as nYear,'0110110100101111' as sCode union all select 2049 as nYear,'1010110110100000' as sCode
 union all
 select 2050 as nYear,'0100101101100011' as sCode union all select 2051 as nYear,'1001001101111111' as sCode union all select 2052 as nYear,'0100100111111000' as sCode union all select 2053 as nYear,'0100100101110000' as sCode union all select 2054 as nYear,'0110010010110000' as sCode union all select 2055 as nYear,'0110100010100110' as sCode union all select 2056 as nYear,'1110101001011111' as sCode union all select 2057 as nYear,'0110101100100000' as sCode union all select 2058 as nYear,'1010011011000100' as sCode union all select 2059 as nYear,'1010101011101111' as sCode
 union all
 select 2060 as nYear,'1001001011100000' as sCode union all select 2061 as nYear,'1101001011100011' as sCode union all select 2062 as nYear,'1100100101100000' as sCode union all select 2063 as nYear,'1101010101010111' as sCode union all select 2064 as nYear,'1101010010100000' as sCode union all select 2065 as nYear,'1101101001010000' as sCode union all select 2066 as nYear,'0101110101010101' as sCode union all select 2067 as nYear,'0101011010100000' as sCode union all select 2068 as nYear,'1010011011010000' as sCode union all select 2069 as nYear,'0101010111010100' as sCode
 union all
 select 2070 as nYear,'0101001011010000' as sCode union all select 2071 as nYear,'1010100110111000' as sCode union all select 2072 as nYear,'1010100101010000' as sCode union all select 2073 as nYear,'1011010010100000' as sCode union all select 2074 as nYear,'1011011010100110' as sCode union all select 2075 as nYear,'1010110101010000' as sCode union all select 2076 as nYear,'0101010110100000' as sCode union all select 2077 as nYear,'1010101110100100' as sCode union all select 2078 as nYear,'1010010110110000' as sCode union all select 2079 as nYear,'0101001010110000' as sCode
 union all
 select 2080 as nYear,'1011001001110011' as sCode union all select 2081 as nYear,'0110100100110000' as sCode union all select 2082 as nYear,'0111001100110111' as sCode union all select 2083 as nYear,'0110101010100000' as sCode union all select 2084 as nYear,'1010110101010000' as sCode union all select 2085 as nYear,'0100101101010101' as sCode union all select 2086 as nYear,'0100101101101111' as sCode union all select 2087 as nYear,'1010010101110000' as sCode union all select 2088 as nYear,'0101010011100100' as sCode union all select 2089 as nYear,'1101001001100000' as sCode
 union all
 select 2090 as nYear,'1110100101101000' as sCode union all select 2091 as nYear,'1101010100100000' as sCode union all select 2092 as nYear,'1101101010100000' as sCode union all select 2093 as nYear,'0110101010100110' as sCode union all select 2094 as nYear,'0101011011011111' as sCode union all select 2095 as nYear,'0100101011100000' as sCode union all select 2096 as nYear,'1010100111010100' as sCode union all select 2097 as nYear,'1010010011010000' as sCode union all select 2098 as nYear,'1101000101010000' as sCode union all select 2099 as nYear,'1111001001010010' as sCode
 union all
 select 2100 as nYear,'1101010100100000' as sCode
 ) as z

 declare @i as integer
 declare @str as varchar(16)
 declare @nDays as integer

 select @nDays=0

 --累加1900到指定日期的上一个年份的天数
 declare @nYear as integer
 declare @sCode as varchar(16)

 DECLARE cur999 SCROLL CURSOR FOR SELECT nYear,sCode FROM #tmplunarInfo WHERE nYear<Year(@pdDate)
 OPEN cur999
 FETCH FIRST FROM cur999 INTO @nYear,@sCode
 WHILE @@FETCH_STATUS = 0
 BEGIN

  select @i=1
  while @i<=12
  begin
      select @nDays = @nDays + 29 + cast(substring(@sCode, @i, 1) as integer)
   select @i=@i+1
  end
  
  if cast(substring(@sCode,13,4) as integer)<>1111 and cast(substring(@sCode,13,4) as integer)<>0
  begin
   select @str=REPLICATE('0',16)
   select @str=sCode from #tmplunarInfo where nYear=@nYear+1
   if substring(@str,13,4)='1111'
    select @nDays=@nDays+30
   else
    select @nDays=@nDays+29
  end

  FETCH cur999 INTO @nYear,@sCode
 END
 
 CLOSE cur999
 DEALLOCATE cur999

 --累加指定日期的第一个年份到上一个月的天数
 select @str=sCode from #tmplunarInfo where nYear=YEAR(@pdDate)
 select @i=1
 while @i<Month(@pdDate)
 begin
    select @nDays = @nDays + 29 + cast(substring(@str, @i, 1) as integer)
  select @i=@i+1
 end

 declare @nMonth as integer
 select @i=13,@nMonth=0
 while @i<=16
 begin
  if substring(@str,@i,1)='1'
  begin
   select @nMonth=@nMonth + POWER(2,16-@i)
  end
  select @i=@i+1
 end 
 if @nMonth<MONTH(@pdDate) And cast(substring(@str,13,4) as integer)<>0
 begin

  select @str=REPLICATE('0',16)
  select @str=sCode from #tmplunarInfo where nYear=@nYear+1
  if substring(@str,13,4)='1111'
   select @nDays=@nDays+30
  else
   select @nDays=@nDays+29

 end
 else if @nMonth=MONTH(@pdDate)
 begin

  If @pbLeapMonth = 1
     select @nDays = @nDays + 29 + cast(substring(@str, MONTH(@pdDate), 1) as integer)

 end

 --累加指定日期当月的天数
 declare @b as bit
 select @b=1
 select @str=sCode from #tmplunarInfo where nYear=YEAR(@pdDate)
 if substring(@str,MONTH(@pdDate),1)='1'   --30
 begin
   if DAY(@pdDate)=31
   begin
     select @b=0
   end
   else
    begin
      select @nDays = @nDays + DAY(@pdDate)
    end
 end
 else --29
 begin
   if DAY(@pdDate)>29
   begin
     select @b=0
   end
   else
    begin
      select @nDays = @nDays + DAY(@pdDate)
    end 
 end

 --换算当前日期
 if @b=1
  select @return=DateAdd(d,@nDays-1,'1900-01-31')
 else
  select @return=null

 drop table #tmplunarInfo

end

--select @return

 

 

 

 

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

 

 

@dDateT   需转换的日期.
exec dbo.p_OldtoNewDate @dDateT,0,@dDTn output
select @dDTn