متد تبدیل تاریخ میلادی به شمسی در sql server
با استفاده از متد زیر می توانید تاریخ میلادی را در SQL Server به تاریخ شمسی تبدیل نمایید.
CREATE FUNCTION [dbo].[Shamsi]
(
@Date1 DATETIME
)
RETURNS varchar(10)
AS
BEGIN DECLARE @ResultVar varchar(10)
declare @Year int
declare @Month int
declare @Day int
declare @PersianYear int
declare @PersianMonth int
declare @PersianDay int
declare @StartMonthGregorianDateInPersianCalendar int=10
declare @StartDayGregorianDateInPersianCalendar int=11
DECLARE @Date NVARCHAR(10)
SET @Date = CONVERT(NVARCHAR(10), CONVERT(DATE , @Date1))
set @Year=convert(int,substring(@Date,1,4))
set @Month=convert(int,substring(@Date,6,2))
set @Day=convert(int,substring(@Date,9,2))
declare @GregorianDayIndex int=0
if(dbo.IsLeapYear(@Year)=1)
set @StartDayGregorianDateInPersianCalendar=11
else
if(dbo.IsLeapYear(@Year-1)=1)
set @StartDayGregorianDateInPersianCalendar=12
else
set @StartDayGregorianDateInPersianCalendar=11
declare @m_index int=1
while @m_index<=@Month-1
begin
set @GregorianDayIndex=@GregorianDayIndex + dbo.NumberOfDaysInMonthGregorian(@Year,@m_index)
set @m_index=@m_index+1
end
set @GregorianDayIndex=@GregorianDayIndex+@Day
if(@GregorianDayIndex>=80)
begin
set @PersianYear=@Year-621
end
else
begin
set @PersianYear=@Year-622
end
declare @mdays int
declare @m int
declare @index int=@GregorianDayIndex
set @m_index=0
while 1=1
begin
if(@m_index<=2)
set @m=@StartMonthGregorianDateInPersianCalendar+@m_index
else
set @m=@m_index-2
set @mdays = dbo.NumberOfDayInMonthPersian(@Year,@m)
if(@m=@StartMonthGregorianDateInPersianCalendar)
set @mdays=@mdays-@StartDayGregorianDateInPersianCalendar+1
if(@index<=@mdays)
begin
set @PersianMonth=@m
if(@m=@StartMonthGregorianDateInPersianCalendar)
set @PersianDay=@index+@StartDayGregorianDateInPersianCalendar-1
else
set @PersianDay=@index
break
end
else
begin
set @index=@index-@mdays
set @m_index=@m_index+1
end
end
set @ResultVar=
convert(varchar(4),@PersianYear)+'/'+
right('0'+convert(varchar(2),@PersianMonth),2)+'/'+
right('0'+convert(varchar(2),@PersianDay),2)
RETURN @ResultVar
END
GO
CREATE FUNCTION [dbo].[IsLeapYear]
(
@Year int
)
RETURNS bit
AS
BEGIN
DECLARE @ResultVar bit
if @Year % 400 = 0
Begin
set @ResultVar=1
end
else if @Year % 100 = 0
Begin
set @ResultVar=0
end
else if @Year % 4 = 0
Begin
set @ResultVar=1
end
else
Begin
set @ResultVar=0
end
RETURN @ResultVar
END
GO
CREATE FUNCTION [dbo].[NumberOfDaysInMonthGregorian]
(
@Year int
,@Month int
)
RETURNS int
AS
BEGIN
DECLARE @ResultVar int
if(@Month<>2)
begin
set @ResultVar=30+((@Month + FLOOR(@Month/8)) % 2)
end
else
begin
if(dbo.IsLeapYear(@Year)=1)
begin
set @ResultVar=29
end
else
begin
set @ResultVar=28
end
end
RETURN @ResultVar
END
GO
CREATE FUNCTION [dbo].[NumberOfDayInMonthPersian]
(
@Year int,
@Month int
)
RETURNS int
AS
BEGIN
DECLARE @ResultVar int
if(@Month<=6)
set @ResultVar=31
else
if(@Month=12)
if(dbo.IsLeapYear(@Year-1)=1)
set @ResultVar=30
else
set @ResultVar=29
else
set @ResultVar=30
RETURN @ResultVar
END
نحوه استفاده
SELECT dbo.Shamsi(GETDATE())
و یا
SELECT [dbo].[Shamsi]('2018/02/01')