تاریخ میلادی به شمسی در sql

1. CREATE FUNCTION [dbo].[Shamsi]
2. (
3. @Date1 DATETIME
4. )
5. RETURNS VARCHAR(10)
6. AS
7. BEGIN    DECLARE @ResultVar VARCHAR(10)
8. DECLARE @YEAR INT
9. DECLARE @MONTH INT
10. DECLARE @DAY INT
11. DECLARE @PersianYear INT
12. DECLARE @PersianMonth INT
13. DECLARE @PersianDay INT
14. DECLARE @StartMonthGregorianDateInPersianCalendar INT=10
15. DECLARE @StartDayGregorianDateInPersianCalendar INT=11
16. DECLARE @DATE NVARCHAR(10)
17. SET @DATE = CONVERT(NVARCHAR(10), CONVERT(DATE , @Date1))
18. SET @YEAR=CONVERT(INT,SUBSTRING(@DATE,1,4))
19. SET @MONTH=CONVERT(INT,SUBSTRING(@DATE,6,2))
20. SET @DAY=CONVERT(INT,SUBSTRING(@DATE,9,2))
21. DECLARE @GregorianDayIndex INT=0
22. IF(dbo.IsLeapYear(@YEAR)=1)
23. SET @StartDayGregorianDateInPersianCalendar=11
24. ELSE
25. IF(dbo.IsLeapYear(@Year-1)=1)
26. SET @StartDayGregorianDateInPersianCalendar=12
27. ELSE
28. SET @StartDayGregorianDateInPersianCalendar=11
29. DECLARE @m_index INT=1
30. while @m_index<=@Month-1
31. BEGIN
32. SET  @GregorianDayIndex=@GregorianDayIndex + dbo.NumberOfDaysInMonthGregorian(@YEAR,@m_index)
33. SET @m_index=@m_index+1
34. END
35. SET @GregorianDayIndex=@GregorianDayIndex+@DAY
36. IF(@GregorianDayIndex>=80)
37. BEGIN
38. SET @PersianYear=@Year-621
39. END
40. ELSE
41. BEGIN
42. SET @PersianYear=@Year-622
43. END
44. DECLARE @mdays INT
45. DECLARE @m INT
46. DECLARE @INDEX INT=@GregorianDayIndex
47. SET @m_index=0
48. while 1=1
49. BEGIN
50. IF(@m_index<=2)
51. SET @m=@StartMonthGregorianDateInPersianCalendar+@m_index
52. ELSE
53. SET @m=@m_index-2
54. SET @mdays = dbo.NumberOfDayInMonthPersian(@YEAR,@m)
55. IF(@m=@StartMonthGregorianDateInPersianCalendar)
56. SET @mdays=@mdays-@StartDayGregorianDateInPersianCalendar+1
57. IF(@index<=@mdays)
58. BEGIN
59. SET @PersianMonth=@m
60. IF(@m=@StartMonthGregorianDateInPersianCalendar)
61. SET @PersianDay=@INDEX+@StartDayGregorianDateInPersianCalendar-1
62. ELSE
63. SET @PersianDay=@INDEX
64. break
65. END
66. ELSE
67. BEGIN
68. SET @INDEX=@index-@mdays
69. SET @m_index=@m_index+1
70. END
71. END
72. SET @ResultVar=CONVERT(VARCHAR(4),@PersianYear)+'/'+RIGHT('0'+CONVERT(VARCHAR(2),@PersianMonth),2)+'/'+RIGHT('0'+CONVERT(VARCHAR(2),@PersianDay),2)
73. RETURN @ResultVar
74. END
75. GO
76. CREATE FUNCTION [dbo].[IsLeapYear]
77. (
78. @YEAR INT
79. )
80. RETURNS bit
81. AS
82. BEGIN
83. DECLARE @ResultVar bit
84. IF @YEAR % 400 = 0
85. BEGIN
86. SET @ResultVar=1
87. END
88. ELSE IF @YEAR % 100 = 0
89. BEGIN
90. SET @ResultVar=0
91. END
92. ELSE IF @YEAR % 4 = 0
93. BEGIN
94. SET @ResultVar=1
95. END
96. ELSE
97. BEGIN
98. SET @ResultVar=0
99. END
100. RETURN @ResultVar
101. END
102. GO
103. CREATE FUNCTION [dbo].[NumberOfDaysInMonthGregorian]
104. (
105. @YEAR INT
106. ,@MONTH INT
107. )
108. RETURNS INT
109. AS
110. BEGIN
111. DECLARE @ResultVar INT
112. IF(@Month<>2)
113. BEGIN
114. SET @ResultVar=30+((@MONTH + FLOOR(@MONTH/8)) % 2)
115. END
116. ELSE
117. BEGIN
118. IF(dbo.IsLeapYear(@YEAR)=1)
119. BEGIN
120. SET @ResultVar=29
121. END
122. ELSE
123. BEGIN
124. SET @ResultVar=28
125. END
126. END
127. RETURN @ResultVar
128. END
129. GO
130. CREATE FUNCTION [dbo].[NumberOfDayInMonthPersian]
131. (
132. @YEAR INT,@MONTH INT
133. )
134. RETURNS INT
135. AS
136. BEGIN
137. DECLARE @ResultVar INT
138. IF(@Month<=6)
139. SET @ResultVar=31
140. ELSE
141. IF(@MONTH=12)
142. IF(dbo.IsLeapYear(@Year-1)=1)
143. SET @ResultVar=30
144. ELSE
145. SET @ResultVar=29
146. ELSE
147. SET @ResultVar=30
148. RETURN @ResultVar
149. END