Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to use SQL to generate a date dimension table with lunar calendar". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Create a calendar

CREATE TABLE SolarData (yearId int not null primary key, data char (7) not null, dataInt int not null)

Insert lunar data

The lunar calendar data contains specific data from 1900 to 2049.

INSERT INTO SolarData SELECT 1900 UNION ALL SELECT 1902, 0x0a570, UNION ALL SELECT 1903, 0x054d5, 21717 UNION ALL SELECT 1904, 0x0d260, 0x0d260, UNION ALL SELECT 1905, 0x0d950, 55632 UNION ALL SELECT 1906, 0x16554, 91476, UNION ALL SELECT 1907, 0x056a0, 0x09ad0, 39632 UNION ALL SELECT 1908, 0x09ad0, 39632 UNION ALL SELECT 1909, 0x055d2, 21970 UNION ALL SELECT, 191970 UNION ALL SELECT 1910, 0x04ae0ae0, 19168 UNION ALL SELECT, 1911, 0x0a5b6, 42422, UNION ALL SELECT 1912x05a4d02, 42192 UNION ALL SELECT 1913, 0x0d250, 42192 UNION ALL SELECT 1914, 0x1d255, 184381 UNION ALL SELECT 1915, 0x0b540, 46400 UNION ALL SELECT 1916, 0x0d6a0, 04944 UNION ALL SELECT 1917, 0x0ada2, 44450 UNION ALL SELECT 1918, 0x095b0, 38320 UNION ALL SELECT, 1919x977, 84343, UNION ALL SELECT 1920, 0x04970, 18800 UNION ALL SELECT 192, 1922, 0x0b4b550, 46261 UNION ALL SELECT 1923, 0x0650, 27216 UNION ALL SELECT, 1924, 0x0640, 27968, UNION ALL SELECT 1925x0640, 109396 UNION ALL SELECT 1926 retro 0x02b60 mr.11104 UNION ALL SELECT 1927 mr.0x09570 pr. 38256 UNION ALL SELECT 1928 pr. 0x052f2. Pr. 21234 UNION ALL SELECT 1929. 0x04970 pr. 18800 UNION ALL SELECT 1930 mr.0x06566 mr.25958 UNION ALL SELECT 1931 mr.0x0d4a0 mr.54432 UNION ALL SELECT 1932 mr.0x0ea50 mr.59984 UNION ALL SELECT 1933 mr.0x06e95pr. 28309 UNION ALL SELECT 1934 and 0x02b606, 11104 UNION ALL SELECT 19686, 0x186e3, and 100067 UNION ALL SELECT, 1937, 0x092e0, 37600 UNION ALL SELECT 1938, 0x0c8d7d7d7d7. 116951 UNION ALL SELECT 1939, 0x0c950, 116951 UNION ALL SELECT 1940, 0x0d4a0, 54432 UNION ALL SELECT 1941, 0x1d8a6, 120998 UNION ALL SELECT 1942, 0x0b550, 46416 UNION ALL SELECT 1943, 0x056a0, 22176 UNION ALL SELECT 1944, 0x1a5b4, 9680 UNION ALL SELECT 1945, 0x092d0, and 37584 UNION ALL SELECT, 1947, 0x0d2d0, 53938, UNION ALL SELECT, 1948, 0x0a950, 43344 UNION ALL SELECT, 1949, 0x0b557, 46423, UNION ALL SELECT, 1950x0x06ca0, 27808, 1951UNION ALL SELECT, 0x0b0b05505504. 46416 UNION ALL SELECT 1952, "0x15355", "0x04da0,"46416 UNION ALL SELECT 1952," UNION ALL SELECT 1953, "0x04da0," UNION ALL SELECT 19572, 1954, "0x0a5d0," 42448 UNION ALL SELECT 1956, "0x14573", "83315 UNION ALL SELECT 1956,"21200 UNION ALL SELECT 1957," 0x0a9a8, "0x0a8," 43432 UNION ALL SELECT 1958, "0x0e950", 59728 UNION ALL SELECT 1959, "0x06aa0," 27296 UNION ALL SELECT 1960x0ae644710 UNION ALL SELECT 1961j, 0x0ab50, "43856 UNION ALL SELECT 1962, 0x0b6050," 19296 UNION ALL SELECT, 1962, "0x0b605," and 19296 UNION ALL SELECT 1963, "0x0aae4aae4," 37448, 1964, and 1964x0aae470ae470. 42352 UNION ALL SELECT 1965, 0x05260, UNION ALL SELECT 1966, 0x0f263, 62051 UNION ALL SELECT 1967, 0x0d950, 55632 UNION ALL SELECT 1968, 0x05b57, 23383 UNION ALL SELECT 1969, 22176 UNION ALL SELECT 1970, 0x096d0, 38608 UNION ALL SELECT 1971 0x04dd5, 199152 UNION ALL SELECT 1972, 0x04ad0, 19152 UNION ALL SELECT 1972192, 0x0d4d44, 54484, UNION ALL SELECT, 1975, 0x0d250, 250, 53840 UNION ALL SELECT, 1976x0d250, 53840 UNION ALL SELECT, 1976x0d55855, 54616 UNION ALL SELECT, 1977x04ad0, 46400 UNION ALL SELECT 1978, 0x0b5a0, 46400 UNION ALL SELECT 1979, 0x195a6, 103846 UNION ALL SELECT 1980, 0x095b0, 18864 UNION ALL SELECT 1981, 0x049b0, 18864 UNION ALL SELECT 1982, 0x0a974, 43380 UNION ALL SELECT 1983, 0x0a4b0, 1983, 42160 UNION ALL SELECT, 1984, 0x06a50, 1985x6a50, 27216 UNION ALL SELECT, 1986x06d40, UNION ALL SELECT 1987, 0xab60, 44870 UNION ALL SELECT, 1988xab060, 43872 UNION ALL SELECT, 1989x0604, 3872 UNION ALL SELECT, 0x0x070,256 UNION ALL SELECT. 19189 UNION ALL SELECT 1991 retro 0x04970 retro 18800 UNION ALL SELECT 1992 repartee 25776 UNION ALL SELECT 1993reel 0x074a3 remark 29859 UNION ALL SELECT 1994 59984 UNION ALL SELECT 1995mil 0x06b58 mr.27480 UNION ALL SELECT 1996 mr.0x055c0pr 21952 UNION ALL SELECT 1997m0x0ab60m438613 UNION ALL SELECT 19999x0x02e0551552 UNION ALL SELECT 2001x0d9545m5556UNION ALL SELECT 2002x0d4a0mm54432 UNION ALL SELECT 2003x0x0da05050 55888 UNION ALL SELECT 2004 UNION ALL SELECT 0x07552 55888 UNION ALL SELECT 2005 0x056a0 reel 22176 UNION ALL SELECT 2006reel 0x0abb7 reel 43959 UNION ALL SELECT 2007remark 0x025d0 remark 9680 UNION ALL SELECT 2008mr.0x092d0meme 37584 UNION ALL SELECT 2009 makeshift 0x0cab5 mould51893 UNION ALL SELECT 2010m0x0a950m43344 UNION ALL SELECT 2011m0x0b4a0m46240 UNION ALL SELECT 20110x0a0a4 47780 UNION ALL SELECT 2013rea0x0ad50and44368 UNION ALL SELECT 2014w0x0d911010x0ad50and21977 UNION ALL SELECT 2015x04ba0lead 1960 UNION ALL SELECT 20162010x0a0a0b0b0b0b0b0b0b05b0b0b0a05x0b0a05x0b0a06x0b0a05x0b0a05x0b0a05x0b0a05x0b0a05x0b0a010x0b0a010x0b0a05x0b0a05x0b0a053344 UNION ALL SELECT 2011m0x0b0a053344 UNION ALL SELECT 2011reward0x0b0a0re43344 UNION ALL SELECT 2011rewarding 0x0b4a0m46240 UNION ALL SELECT 2012b4a0req47780 UNION ALL SELECT 2013rea0x0ad50and44368 UNION ALL SELECT 201410x0d99ba0m21977 UNION ALL SELECT 2015x04ba0m21977 UNION ALL SELECT 2015x04ba0mm433 42416 UNION ALL SELECT 2017 0x15176reel 86390 UNION ALL SELECT 2018 repertoire 21168 UNION ALL SELECT 2019 reel 0x0a930 mahogany 43312 UNION ALL SELECT 2054 360 UNION ALL SELECT 2021 pageant 0x06aa0m27296 UNION ALL SELECT 2022 prima 44368 UNION ALL SELECT 2023m0x05b522m23378 UNION ALL SELECT 2024m0x04b60m19296 UNION ALL SELECT 20x0a6e619296 UNION ALL SELECT 2026, 0x0a6e0e62272026 UNION ALL SELECT 2026, 0x0a4e0e026053856 UNION ALL SELECT 2028x0e65m6000295x0e630d5306 54576 UNION ALL SELECT 2030 UNION ALL SELECT 2031 reel 0x076a3 relic 30371 UNION ALL SELECT 2032 remark 0x096d0 reel 38608 UNION ALL SELECT 203 reprimanding 0x04bd7 1915 UNION ALL SELECT 2034 0x04ad0 pamphlet 19152 UNION ALL SELECT 2035 UNION ALL SELECT 2035 reproducing 0x0a4d0mics 42192 UNION ALL SELECT 2036 mics 118966 UNION ALL SELECT 207mn0x0d250 53840 UNION ALL SELECT 2038 UNION ALL SELECT 0x0d520540 UNION ALL SELECT 2060x0dd45556645 UNION ALL SELECT 2040req0x0b5a0m6464UNION ALL SELECT 204x0x0d02224 UNION ALL SELECT 204x0b05210104x0b0b0544x0x0b044x0x0b04x0x0x0b04x0x0d04x0x0d04x0x0d022x0x0b04x0x0b04x0x0b04x0x0x0x0b0x0x0x0x0x0x0x0x0b0d0x0x0x0x0d0x0x0d0x0x0d02x0x0d02x0x0d0x0x0d0x0x0d02x0x0x0d02x0x0b610x0x0b610x0x0b610x0x0b610x0x0b610x0x0b610x0x0b62x0x0b62x0x0b62x0x0b62x0x0b62x0x0b62x0x0b62x0x0d0d0 21938 UNION ALL SELECT 2043, 0x049b0, 21938 UNION ALL SELECT 2044, 0x0a577, 42359 UNION ALL SELECT 2045, 0x0a4b0, 42160 UNION ALL SELECT 2046, 0x0aa50, 43600 UNION ALL SELECT, 2047, 0x1b255, 111189 UNION ALL SELECT 2048, 0x06d20, 27936 UNION ALL SELECT 2049, 0x0ada0, 44448.

(hint: you can swipe the code left and right)

Calculate the specific lunar calendar

There are fixed rules for the calculation of the lunar calendar, and we use the following algorithm to calculate the lunar calendar corresponding to the Gregorian calendar.

Note: the following algorithms come from the network

Create function dbo.fn_GetLunar (@ solarday datetime) returns nvarchar (30) as begin declare @ soldata int declare @ offset int declare @ ilunar int declare @ i int declare @ j int declare @ ydays int declare @ mleap int declare @ mleap1 int declare @ mleapnum int declare @ temp int declare @ year nvarchar (10) declare @ month nvarchar (10) declare @ day nvarchar (10) declare @ chinesenum nvarchar (10) declare @ outputdate nvarchar (30) set @ offset=datediff (day,'1900-01-30' @ solarday)-- determine the beginning of the lunar year set @ icalendar 1900-- set @ offset=@soldata while @ i0 begin set @ ydays=348 set @ mleapnum=0 select @ ilunar=dataint from solardata where yearid=@i-- return the total number of days of the lunar year set @ juni32768 while @ j > 8 begin if @ ilunar & @ j > 0 set @ ydays=@ydays+1 set @ j=@j/2 end-- return the month 1-12 in the lunar calendar, and no leap return 0 set @ mleap = @ ilunar & 15-- return the number of days of the leap month of the lunar year Add to the total number of days of the year if @ mleap > 0 begin if @ ilunar & 65536 > 0 set @ mleapnum=30 else set @ mleapnum=29 set @ offset=@offset-@ydays set @ i=@i+1 end if @ offset 0 begin-- to determine the run month set @ mdays=0 if (@ mleap > 0 and @ I = (@ mleap+1) and @ bleap=0) begin-- is run month set @ i=@i-1 set @ bleap=1 set @ mleap1= @ mleap-return the leap month of the lunar year Days if @ ilunar & 65536 > 0 set @ mdays = 30 else set @ mdays = 29 end else-not Runyue begin set @ juni1 set @ temp = 65536 while @ j 0 set @ mdays = 30 else set @ mdays = 29 end-release Runyue if @ bleap=1 and @ I = (@ mleap+1) set @ bleap=0 set @ offset=@offset-@mdays set @ i=@i+1 end if @ offset 0 select @ outputdate=isnull (@ outputdate) '') + substring (@ chinesenum,left (@ year,1) + 1), @ year=stuff (@ year,1,1,'') set @ outputdate=@outputdate+N' year'+ case @ mleap1 when @ month then N'run 'else' 'end if cast (@ month as int) = 10 set @ outputdate=@outputdate+ case @ month when' 10 'then N' 10' when 11 then N' 11 'else N' 12' end set @ outputdate=@outputdate+N' month'if cast (@ day as int)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.