Author:水如烟
新的节假日加入了农历日期,这也是在保护发扬国粹吧。
农历如何制定的,估计大多数人是不懂的了,本人也是。
以下数据包括算法都来源于网络资源。
说明:
一、农历的基本数据有两套,第二套可以用到2100年。其中一二套1900-2049年部分,有些不相同。我默认选用第一套数据。
二、原来的代码将农历日期转为Datetime来处理,其实是不行的,因为农历二月可能有30天的。所以,我转为八位字符来处理。
三、输出的节日(节气)信息中,只含农历。若要包含公历节假日,打算在另一个函数中体现。
USE
[
master
]
GO
IF NOT EXISTS ( SELECT name FROM sys.databases WHERE name = N ' LzmtwDb ' )
CREATE DATABASE [ LzmtwDb ]
GO
USE LzmtwDb
GO
IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = N ' Chinese ' )
EXEC sys.sp_executesql N ' CREATE SCHEMA [Chinese] AUTHORIZATION [dbo] '
GO
IF NOT EXISTS ( SELECT name FROM sys.databases WHERE name = N ' LzmtwDb ' )
CREATE DATABASE [ LzmtwDb ]
GO
USE LzmtwDb
GO
IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = N ' Chinese ' )
EXEC sys.sp_executesql N ' CREATE SCHEMA [Chinese] AUTHORIZATION [dbo] '
GO
IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID (N ' [Chinese].[LunisolarSolarTerm] ' ) AND type in (N ' U ' ))
BEGIN
CREATE TABLE [ Chinese ] . [ LunisolarSolarTerm ] (
[ ID ] [ smallint ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ Name ] [ nchar ] ( 2 ) NOT NULL ,
[ Value ] [ int ] NULL
) ON [ PRIMARY ]
INSERT INTO [ Chinese ] . [ LunisolarSolarTerm ]
SELECT N ' 小寒 ' , 0 UNION ALL
SELECT N ' 大寒 ' , 21208 UNION ALL
SELECT N ' 立春 ' , 42467 UNION ALL
SELECT N ' 雨水 ' , 63836 UNION ALL
SELECT N ' 惊蛰 ' , 85337 UNION ALL
SELECT N ' 春分 ' , 107014 UNION ALL
SELECT N ' 清明 ' , 128867 UNION ALL
SELECT N ' 谷雨 ' , 150921 UNION ALL
SELECT N ' 立夏 ' , 173149 UNION ALL
SELECT N ' 小满 ' , 195551 UNION ALL
SELECT N ' 芒种 ' , 218072 UNION ALL
SELECT N ' 夏至 ' , 240693 UNION ALL
SELECT N ' 小暑 ' , 263343 UNION ALL
SELECT N ' 大暑 ' , 285989 UNION ALL
SELECT N ' 立秋 ' , 308563 UNION ALL
SELECT N ' 处暑 ' , 331033 UNION ALL
SELECT N ' 白露 ' , 353350 UNION ALL
SELECT N ' 秋分 ' , 375494 UNION ALL
SELECT N ' 寒露 ' , 397447 UNION ALL
SELECT N ' 霜降 ' , 419210 UNION ALL
SELECT N ' 立冬 ' , 440795 UNION ALL
SELECT N ' 小雪 ' , 462224 UNION ALL
SELECT N ' 大雪 ' , 483532 UNION ALL
SELECT N ' 冬至 ' , 504758
END
IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID (N ' [Chinese].[LunisolarSolarTerm] ' ) AND type in (N ' U ' ))
BEGIN
CREATE TABLE [ Chinese ] . [ LunisolarSolarTerm ] (
[ ID ] [ smallint ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ Name ] [ nchar ] ( 2 ) NOT NULL ,
[ Value ] [ int ] NULL
) ON [ PRIMARY ]
INSERT INTO [ Chinese ] . [ LunisolarSolarTerm ]
SELECT N ' 小寒 ' , 0 UNION ALL
SELECT N ' 大寒 ' , 21208 UNION ALL
SELECT N ' 立春 ' , 42467 UNION ALL
SELECT N ' 雨水 ' , 63836 UNION ALL
SELECT N ' 惊蛰 ' , 85337 UNION ALL
SELECT N ' 春分 ' , 107014 UNION ALL
SELECT N ' 清明 ' , 128867 UNION ALL
SELECT N ' 谷雨 ' , 150921 UNION ALL
SELECT N ' 立夏 ' , 173149 UNION ALL
SELECT N ' 小满 ' , 195551 UNION ALL
SELECT N ' 芒种 ' , 218072 UNION ALL
SELECT N ' 夏至 ' , 240693 UNION ALL
SELECT N ' 小暑 ' , 263343 UNION ALL
SELECT N ' 大暑 ' , 285989 UNION ALL
SELECT N ' 立秋 ' , 308563 UNION ALL
SELECT N ' 处暑 ' , 331033 UNION ALL
SELECT N ' 白露 ' , 353350 UNION ALL
SELECT N ' 秋分 ' , 375494 UNION ALL
SELECT N ' 寒露 ' , 397447 UNION ALL
SELECT N ' 霜降 ' , 419210 UNION ALL
SELECT N ' 立冬 ' , 440795 UNION ALL
SELECT N ' 小雪 ' , 462224 UNION ALL
SELECT N ' 大雪 ' , 483532 UNION ALL
SELECT N ' 冬至 ' , 504758
END
GO
IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID (N ' [Chinese].[LunisolarDatas] ' ) AND type in (N ' U ' ))
BEGIN
CREATE TABLE [ Chinese ] . [ LunisolarDatas ] (
[ yearId ] [ int ] NOT NULL ,
[ data ] [ char ] ( 7 ) NULL ,
[ dataInt ] [ int ] NULL ,
[ Data1 ] [ varchar ] ( 7 ) NULL ,
[ DataInt1 ] [ int ] NULL
) ON [ PRIMARY ]
INSERT INTO [ Chinese ] . [ LunisolarDatas ]
SELECT 1900 , ' 0x04bd8 ' , 19416 , ' 0x4bd8 ' , 19416 UNION ALL
SELECT 1901 , ' 0x04ae0 ' , 19168 , ' 0x4ae0 ' , 19168 UNION ALL
SELECT 1902 , ' 0x0a570 ' , 42352 , ' 0xa570 ' , 42352 UNION ALL
SELECT 1903 , ' 0x054d5 ' , 21717 , ' 0x54d5 ' , 21717 UNION ALL
SELECT 1904 , ' 0x0d260 ' , 53856 , ' 0xd260 ' , 53856 UNION ALL
SELECT 1905 , ' 0x0d950 ' , 55632 , ' 0xd950 ' , 55632 UNION ALL
SELECT 1906 , ' 0x16554 ' , 91476 , ' 0x5554 ' , 21844 UNION ALL
SELECT 1907 , ' 0x056a0 ' , 22176 , ' 0x56af ' , 22191 UNION ALL
SELECT 1908 , ' 0x09ad0 ' , 39632 , ' 0x9ad0 ' , 39632 UNION ALL
SELECT 1909 , ' 0x055d2 ' , 21970 , ' 0x55d2 ' , 21970 UNION ALL
SELECT 1910 , ' 0x04ae0 ' , 19168 , ' 0x4ae0 ' , 19168 UNION ALL
SELECT 1911 , ' 0x0a5b6 ' , 42422 , ' 0xa5b6 ' , 42422 UNION ALL
SELECT 1912 , ' 0x0a4d0 ' , 42192 , ' 0xa4d0 ' , 42192 UNION ALL
SELECT 1913 , ' 0x0d250 ' , 53840 , ' 0xd250 ' , 53840 UNION ALL
SELECT 1914 , ' 0x1d255 ' , 119381 , ' 0xd295 ' , 53909 UNION ALL
SELECT 1915 , ' 0x0b540 ' , 46400 , ' 0xb54f ' , 46415 UNION ALL
SELECT 1916 , ' 0x0d6a0 ' , 54944 , ' 0xd6a0 ' , 54944 UNION ALL
SELECT 1917 , ' 0x0ada2 ' , 44450 , ' 0xada2 ' , 44450 UNION ALL
SELECT 1918 , ' 0x095b0 ' , 38320 , ' 0x95b0 ' , 38320 UNION ALL
SELECT 1919 , ' 0x14977 ' , 84343 , ' 0x4977 ' , 18807 UNION ALL
SELECT 1920 , ' 0x04970 ' , 18800 , ' 0x497f ' , 18815 UNION ALL
SELECT 1921 , ' 0x0a4b0 ' , 42160 , ' 0xa4b0 ' , 42160 UNION ALL
SELECT 1922 , ' 0x0b4b5 ' , 46261 , ' 0xb4b5 ' , 46261 UNION ALL
SELECT 1923 , ' 0x06a50 ' , 27216 , ' 0x6a50 ' , 27216 UNION ALL
SELECT 1924 , ' 0x06d40 ' , 27968 , ' 0x6d40 ' , 27968 UNION ALL
SELECT 1925 , ' 0x1ab54 ' , 109396 , ' 0xab54 ' , 43860 UNION ALL
SELECT 1926 , ' 0x02b60 ' , 11104 , ' 0x2b6f ' , 11119 UNION ALL
SELECT 1927 , ' 0x09570 ' , 38256 , ' 0x9570 ' , 38256 UNION ALL
SELECT 1928 , ' 0x052f2 ' , 21234 , ' 0x52f2 ' , 21234 UNION ALL
SELECT 1929 , ' 0x04970 ' , 18800 , ' 0x4970 ' , 18800 UNION ALL
SELECT 1930 , ' 0x06566 ' , 25958 , ' 0x6566 ' , 25958 UNION ALL
SELECT 1931 , ' 0x0d4a0 ' , 54432 , ' 0xd4a0 ' , 54432 UNION ALL
SELECT 1932 , ' 0x0ea50 ' , 59984 , ' 0xea50 ' , 59984 UNION ALL
SELECT 1933 , ' 0x06e95 ' , 28309 , ' 0x6a95 ' , 27285 UNION ALL
SELECT 1934 , ' 0x05ad0 ' , 23248 , ' 0x5adf ' , 23263 UNION ALL
SELECT 1935 , ' 0x02b60 ' , 11104 , ' 0x2b60 ' , 11104 UNION ALL
SELECT 1936 , ' 0x186e3 ' , 100067 , ' 0x86e3 ' , 34531 UNION ALL
SELECT 1937 , ' 0x092e0 ' , 37600 , ' 0x92ef ' , 37615 UNION ALL
SELECT 1938 , ' 0x1c8d7 ' , 116951 , ' 0xc8d7 ' , 51415 UNION ALL
SELECT 1939 , ' 0x0c950 ' , 51536 , ' 0xc95f ' , 51551
IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID (N ' [Chinese].[LunisolarDatas] ' ) AND type in (N ' U ' ))
BEGIN
CREATE TABLE [ Chinese ] . [ LunisolarDatas ] (
[ yearId ] [ int ] NOT NULL ,
[ data ] [ char ] ( 7 ) NULL ,
[ dataInt ] [ int ] NULL ,
[ Data1 ] [ varchar ] ( 7 ) NULL ,
[ DataInt1 ] [ int ] NULL
) ON [ PRIMARY ]
INSERT INTO [ Chinese ] . [ LunisolarDatas ]
SELECT 1900 , ' 0x04bd8 ' , 19416 , ' 0x4bd8 ' , 19416 UNION ALL
SELECT 1901 , ' 0x04ae0 ' , 19168 , ' 0x4ae0 ' , 19168 UNION ALL
SELECT 1902 , ' 0x0a570 ' , 42352 , ' 0xa570 ' , 42352 UNION ALL
SELECT 1903 , ' 0x054d5 ' , 21717 , ' 0x54d5 ' , 21717 UNION ALL
SELECT 1904 , ' 0x0d260 ' , 53856 , ' 0xd260 ' , 53856 UNION ALL
SELECT 1905 , ' 0x0d950 ' , 55632 , ' 0xd950 ' , 55632 UNION ALL
SELECT 1906 , ' 0x16554 ' , 91476 , ' 0x5554 ' , 21844 UNION ALL
SELECT 1907 , ' 0x056a0 ' , 22176 , ' 0x56af ' , 22191 UNION ALL
SELECT 1908 , ' 0x09ad0 ' , 39632 , ' 0x9ad0 ' , 39632 UNION ALL
SELECT 1909 , ' 0x055d2 ' , 21970 , ' 0x55d2 ' , 21970 UNION ALL
SELECT 1910 , ' 0x04ae0 ' , 19168 , ' 0x4ae0 ' , 19168 UNION ALL
SELECT 1911 , ' 0x0a5b6 ' , 42422 , ' 0xa5b6 ' , 42422 UNION ALL
SELECT 1912 , ' 0x0a4d0 ' , 42192 , ' 0xa4d0 ' , 42192 UNION ALL
SELECT 1913 , ' 0x0d250 ' , 53840 , ' 0xd250 ' , 53840 UNION ALL
SELECT 1914 , ' 0x1d255 ' , 119381 , ' 0xd295 ' , 53909 UNION ALL
SELECT 1915 , ' 0x0b540 ' , 46400 , ' 0xb54f ' , 46415 UNION ALL
SELECT 1916 , ' 0x0d6a0 ' , 54944 , ' 0xd6a0 ' , 54944 UNION ALL
SELECT 1917 , ' 0x0ada2 ' , 44450 , ' 0xada2 ' , 44450 UNION ALL
SELECT 1918 , ' 0x095b0 ' , 38320 , ' 0x95b0 ' , 38320 UNION ALL
SELECT 1919 , ' 0x14977 ' , 84343 , ' 0x4977 ' , 18807 UNION ALL
SELECT 1920 , ' 0x04970 ' , 18800 , ' 0x497f ' , 18815 UNION ALL
SELECT 1921 , ' 0x0a4b0 ' , 42160 , ' 0xa4b0 ' , 42160 UNION ALL
SELECT 1922 , ' 0x0b4b5 ' , 46261 , ' 0xb4b5 ' , 46261 UNION ALL
SELECT 1923 , ' 0x06a50 ' , 27216 , ' 0x6a50 ' , 27216 UNION ALL
SELECT 1924 , ' 0x06d40 ' , 27968 , ' 0x6d40 ' , 27968 UNION ALL
SELECT 1925 , ' 0x1ab54 ' , 109396 , ' 0xab54 ' , 43860 UNION ALL
SELECT 1926 , ' 0x02b60 ' , 11104 , ' 0x2b6f ' , 11119 UNION ALL
SELECT 1927 , ' 0x09570 ' , 38256 , ' 0x9570 ' , 38256 UNION ALL
SELECT 1928 , ' 0x052f2 ' , 21234 , ' 0x52f2 ' , 21234 UNION ALL
SELECT 1929 , ' 0x04970 ' , 18800 , ' 0x4970 ' , 18800 UNION ALL
SELECT 1930 , ' 0x06566 ' , 25958 , ' 0x6566 ' , 25958 UNION ALL
SELECT 1931 , ' 0x0d4a0 ' , 54432 , ' 0xd4a0 ' , 54432 UNION ALL
SELECT 1932 , ' 0x0ea50 ' , 59984 , ' 0xea50 ' , 59984 UNION ALL
SELECT 1933 , ' 0x06e95 ' , 28309 , ' 0x6a95 ' , 27285 UNION ALL
SELECT 1934 , ' 0x05ad0 ' , 23248 , ' 0x5adf ' , 23263 UNION ALL
SELECT 1935 , ' 0x02b60 ' , 11104 , ' 0x2b60 ' , 11104 UNION ALL
SELECT 1936 , ' 0x186e3 ' , 100067 , ' 0x86e3 ' , 34531 UNION ALL
SELECT 1937 , ' 0x092e0 ' , 37600 , ' 0x92ef ' , 37615 UNION ALL
SELECT 1938 , ' 0x1c8d7 ' , 116951 , ' 0xc8d7 ' , 51415 UNION ALL
SELECT 1939 , ' 0x0c950 ' , 51536 , ' 0xc95f ' , 51551