仓酷云

标题: MSSQL网页设计阳历转农历 [打印本页]

作者: 飘飘悠悠 时间: 2015-1-16 22:23
标题: MSSQL网页设计阳历转农历
Memory所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失由于夏历的日期，是由地理学家推算出来的，到如今只要到2049年的，今后的有了还能够到场！
CREATETABLESolarData
(
yearIdintnotnull,
datachar(7)notnull,
dataIntintnotnull
)
--拔出数据
INSERTINTO
SolarDataSELECT1900,0x04bd8,19416UNIONALLSELECT1901,0x04ae0,19168
UNIONALLSELECT1902,0x0a570,42352UNIONALLSELECT1903,0x054d5,21717
UNIONALLSELECT1904,0x0d260,53856UNIONALLSELECT1905,0x0d950,55632
UNIONALLSELECT1906,0x16554,91476UNIONALLSELECT1907,0x056a0,22176
UNIONALLSELECT1908,0x09ad0,39632UNIONALLSELECT1909,0x055d2,21970
UNIONALLSELECT1910,0x04ae0,19168UNIONALLSELECT1911,0x0a5b6,42422
UNIONALLSELECT1912,0x0a4d0,42192UNIONALLSELECT1913,0x0d250,53840
UNIONALLSELECT1914,0x1d255,119381UNIONALLSELECT1915,0x0b540,46400
UNIONALLSELECT1916,0x0d6a0,54944UNIONALLSELECT1917,0x0ada2,44450
UNIONALLSELECT1918,0x095b0,38320UNIONALLSELECT1919,0x14977,84343
UNIONALLSELECT1920,0x04970,18800UNIONALLSELECT1921,0x0a4b0,42160
UNIONALLSELECT1922,0x0b4b5,46261UNIONALLSELECT1923,0x06a50,27216
UNIONALLSELECT1924,0x06d40,27968UNIONALLSELECT1925,0x1ab54,109396
UNIONALLSELECT1926,0x02b60,11104UNIONALLSELECT1927,0x09570,38256
UNIONALLSELECT1928,0x052f2,21234UNIONALLSELECT1929,0x04970,18800
UNIONALLSELECT1930,0x06566,25958UNIONALLSELECT1931,0x0d4a0,54432
UNIONALLSELECT1932,0x0ea50,59984UNIONALLSELECT1933,0x06e95,28309
UNIONALLSELECT1934,0x05ad0,23248UNIONALLSELECT1935,0x02b60,11104
UNIONALLSELECT1936,0x186e3,100067UNIONALLSELECT1937,0x092e0,37600
UNIONALLSELECT1938,0x1c8d7,116951UNIONALLSELECT1939,0x0c950,51536
UNIONALLSELECT1940,0x0d4a0,54432UNIONALLSELECT1941,0x1d8a6,120998
UNIONALLSELECT1942,0x0b550,46416UNIONALLSELECT1943,0x056a0,22176
UNIONALLSELECT1944,0x1a5b4,107956UNIONALLSELECT1945,0x025d0,9680
UNIONALLSELECT1946,0x092d0,37584UNIONALLSELECT1947,0x0d2b2,53938
UNIONALLSELECT1948,0x0a950,43344UNIONALLSELECT1949,0x0b557,46423
UNIONALLSELECT1950,0x06ca0,27808UNIONALLSELECT1951,0x0b550,46416
UNIONALLSELECT1952,0x15355,86869UNIONALLSELECT1953,0x04da0,19872
UNIONALLSELECT1954,0x0a5d0,42448UNIONALLSELECT1955,0x14573,83315
UNIONALLSELECT1956,0x052d0,21200UNIONALLSELECT1957,0x0a9a8,43432
UNIONALLSELECT1958,0x0e950,59728UNIONALLSELECT1959,0x06aa0,27296
UNIONALLSELECT1960,0x0aea6,44710UNIONALLSELECT1961,0x0ab50,43856
UNIONALLSELECT1962,0x04b60,19296UNIONALLSELECT1963,0x0aae4,43748
UNIONALLSELECT1964,0x0a570,42352UNIONALLSELECT1965,0x05260,21088
UNIONALLSELECT1966,0x0f263,62051UNIONALLSELECT1967,0x0d950,55632
UNIONALLSELECT1968,0x05b57,23383UNIONALLSELECT1969,0x056a0,22176
UNIONALLSELECT1970,0x096d0,38608UNIONALLSELECT1971,0x04dd5,19925
UNIONALLSELECT1972,0x04ad0,19152UNIONALLSELECT1973,0x0a4d0,42192
UNIONALLSELECT1974,0x0d4d4,54484UNIONALLSELECT1975,0x0d250,53840
UNIONALLSELECT1976,0x0d558,54616UNIONALLSELECT1977,0x0b540,46400
UNIONALLSELECT1978,0x0b5a0,46496UNIONALLSELECT1979,0x195a6,103846
UNIONALLSELECT1980,0x095b0,38320UNIONALLSELECT1981,0x049b0,18864
UNIONALLSELECT1982,0x0a974,43380UNIONALLSELECT1983,0x0a4b0,42160
UNIONALLSELECT1984,0x0b27a,45690UNIONALLSELECT1985,0x06a50,27216
UNIONALLSELECT1986,0x06d40,27968UNIONALLSELECT1987,0x0af46,44870
UNIONALLSELECT1988,0x0ab60,43872UNIONALLSELECT1989,0x09570,38256
UNIONALLSELECT1990,0x04af5,19189UNIONALLSELECT1991,0x04970,18800
UNIONALLSELECT1992,0x064b0,25776UNIONALLSELECT1993,0x074a3,29859
UNIONALLSELECT1994,0x0ea50,59984UNIONALLSELECT1995,0x06b58,27480
UNIONALLSELECT1996,0x055c0,21952UNIONALLSELECT1997,0x0ab60,43872
UNIONALLSELECT1998,0x096d5,38613UNIONALLSELECT1999,0x092e0,37600
UNIONALLSELECT2000,0x0c960,51552UNIONALLSELECT2001,0x0d954,55636
UNIONALLSELECT2002,0x0d4a0,54432UNIONALLSELECT2003,0x0da50,55888
UNIONALLSELECT2004,0x07552,30034UNIONALLSELECT2005,0x056a0,22176
UNIONALLSELECT2006,0x0abb7,43959UNIONALLSELECT2007,0x025d0,9680
UNIONALLSELECT2008,0x092d0,37584UNIONALLSELECT2009,0x0cab5,51893
UNIONALLSELECT2010,0x0a950,43344UNIONALLSELECT2011,0x0b4a0,46240
UNIONALLSELECT2012,0x0baa4,47780UNIONALLSELECT2013,0x0ad50,44368
UNIONALLSELECT2014,0x055d9,21977UNIONALLSELECT2015,0x04ba0,19360
UNIONALLSELECT2016,0x0a5b0,42416UNIONALLSELECT2017,0x15176,86390
UNIONALLSELECT2018,0x052b0,21168UNIONALLSELECT2019,0x0a930,43312
UNIONALLSELECT2020,0x07954,31060UNIONALLSELECT2021,0x06aa0,27296
UNIONALLSELECT2022,0x0ad50,44368UNIONALLSELECT2023,0x05b52,23378
UNIONALLSELECT2024,0x04b60,19296UNIONALLSELECT2025,0x0a6e6,42726
UNIONALLSELECT2026,0x0a4e0,42208UNIONALLSELECT2027,0x0d260,53856
UNIONALLSELECT2028,0x0ea65,60005UNIONALLSELECT2029,0x0d530,54576
UNIONALLSELECT2030,0x05aa0,23200UNIONALLSELECT2031,0x076a3,30371
UNIONALLSELECT2032,0x096d0,38608UNIONALLSELECT2033,0x04bd7,19415
UNIONALLSELECT2034,0x04ad0,19152UNIONALLSELECT2035,0x0a4d0,42192
UNIONALLSELECT2036,0x1d0b6,118966UNIONALLSELECT2037,0x0d250,53840
UNIONALLSELECT2038,0x0d520,54560UNIONALLSELECT2039,0x0dd45,56645
UNIONALLSELECT2040,0x0b5a0,46496UNIONALLSELECT2041,0x056d0,22224
UNIONALLSELECT2042,0x055b2,21938UNIONALLSELECT2043,0x049b0,18864
UNIONALLSELECT2044,0x0a577,42359UNIONALLSELECT2045,0x0a4b0,42160
UNIONALLSELECT2046,0x0aa50,43600UNIONALLSELECT2047,0x1b255,111189
UNIONALLSELECT2048,0x06d20,27936UNIONALLSELECT2049,0x0ada0,44448

使用存储历程：
CREATEPROCEDUREGetLunar_zhangzs
@solarDayDATETIME
AS
DECLARE@solDataint
DECLARE@offsetint
DECLARE@iLunarint
DECLARE@iINT
DECLARE@jINT
DECLARE@yDaysint
DECLARE@mDaysint
DECLARE@mLeapint
DECLARE@mLeapNumint
DECLARE@bLeapsmallint
DECLARE@tempint
DECLARE@YEARINT
DECLARE@MONTHINT
DECLARE@DAYINT
DECLARE@OUTPUTDATEvarchar(10)

--包管传出去的日期是不带工夫
SET@solarDay=cast(@solarDayASchar(10))
SET@offset=CAST(@solarDay-1900-01-30ASINT)

--断定夏历年入手下手
SET@i=1900
--SET@offset=@solData
WHILE@i<2050AND@offset>0
BEGIN
SET@yDays=348
SET@mLeapNum=0
SELECT@iLunar=dataIntFROMSolarDataWHEREyearId=@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=dataIntFROMSolarDataWHEREyearId=@YEAR

--判别谁人月是润月
SET@mLeap=@iLunar&15
SET@bLeap=0

WHILE@i<13AND@offset>0
BEGIN
--判别润月
SET@mDays=0
IF(@mLeap>0AND@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=1AND@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

SET@OUTPUTDATE=convert(varchar(10),CAST((CAST(@YEARASVARCHAR(4))+-+CAST(@MONTHASVARCHAR(2))+-+CAST(@DAYASVARCHAR(2)))ASDATETIME),120)
selectconvert(varchar(10),@solarDay,120)as阳历
,cast(dbo.f_num_str(year(@OUTPUTDATE))asvarchar(8))+年|+
casewhendatalength(dbo.f_num_str(month(@OUTPUTDATE)))=4thencasewhenleft(dbo.f_num_str(month(@OUTPUTDATE)),1)一
thenleft(month(@OUTPUTDATE),1)elseend+十+casewhenright(dbo.f_num_str(month(@OUTPUTDATE)),1)=零thenelseright(dbo.f_num_str(month(@OUTPUTDATE)),1)endelse
cast(dbo.f_num_str(month(@OUTPUTDATE))asvarchar(4))end+月|
+casewhendatalength(dbo.f_num_str(day(@OUTPUTDATE)))=4thencasewhencast(dbo.f_num_str(day(@OUTPUTDATE))asvarchar(4))=一零then初else(casewhenleft(cast(dbo.f_num_str(day(@OUTPUTDATE))asvarchar(4)),1)一
thenleft(cast(dbo.f_num_str(day(@OUTPUTDATE))asvarchar(4)),1)elseend)end+十+casewhenright(cast(dbo.f_num_str(day(@OUTPUTDATE))asvarchar(4)),1)=零thenelse
right(cast(dbo.f_num_str(day(@OUTPUTDATE))asvarchar(4)),1)endelse初+cast(dbo.f_num_str(day(@OUTPUTDATE))asvarchar(4))endas农历
GO
--注重：存储过程当中利用了自界说函数f_num_str
--加此函数的目标是把数字换年夜写
CREATEFUNCTION[dbo].[f_num_str](@numint)
RETURNSvarchar(100)
AS
BEGIN
DECLARE@n_strVARCHAR(20),@reVARCHAR(20),@iint
SELECT@n_str=cast(@numasvarchar),@i=1,@re=
WHILE@i<=len(@n_str)
BEGIN
SET@re=@re+SUBSTRING(零一二三四五六七八九,CAST(SUBSTRING(@n_str,@i,1)ASint)+1,1)
SET@i=@i+1
END
RETURN@re
END

--零壹贰叁肆伍陆柒捌玖
--挪用存储历程
execGetLunar_zhangzs1978-07-02

--前往了局：

阳历农历
------------------------------------
1978-07-02一九七八年|蒲月|二十七
使为了数据安全，我们搭建了主从。但实时主从备份只能防止硬件问题，比如主库的硬盘损坏。但对于误操作，则无能为力。比如在主库误删一张表，或者一个update语句没有指定where条件，导致全表被更新。

作者: 因胸联盟 时间: 2015-1-19 09:57
发几份SQL课件，以飨阅者

作者: admin 时间: 2015-1-19 09:57
然后最好有实践机会，能够把实践到的和实践结合起来，其实理论思考是个非常困扰和痛苦的事情

作者: 分手快乐 时间: 2015-1-27 14:11
比如日志传送、比如集群。。。

作者: 深爱那片海 时间: 2015-2-5 10:46
SQLServer的异构移植功能个人感觉最好了。（如果对比过SQLServer的链接服务器和Oracle的透明网关的朋友会发现SQLServer的sp_addlinkedserver(openquery)异构数据库系列比Oracle真是强太多了。）

作者: 小妖女 时间: 2015-2-11 10:14
每天坚持做不一样的是，认真做笔录，定时复习。一个月你就可以有一定的收获。当然如果你想在sql方面有一定的造诣，你少不了需要看很多很多的书籍了。

作者: 飘灵儿 时间: 2015-3-2 11:08
XML字段类型更好的解决了XML数据的操作。XQuery确实不错，但是个人对其没好感。（CSDN的开发者应该是相当的熟了！）

作者: 若相依 时间: 2015-3-11 03:52
另一个是把SQL语句写到服务器端,就是所谓的SP(存储过程);

作者: 再见西城 时间: 2015-3-17 20:10
原来公司用过MYSQL自己也只是建个表写个SQL

作者: 精灵巫婆 时间: 2015-3-25 00:50
如果你是从“学习某一种数据库应用软件，从而获得应聘的资本和工作机会”的角度来问的话。

欢迎光临仓酷云 (http://ckuyun.com/)