/****** Object: UserDefinedFunction [dbo].[MiladiTOShamsi] Script Date: 10/15/2014 04:05:51 ب.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[MiladiTOShamsi] (@MDate DateTime)
RETURNS Varchar(10)
AS
BEGIN
DECLARE @SYear as Integer
DECLARE @SMonth as Integer
DECLARE @SDay as Integer
DECLARE @AllDays as float
DECLARE @ShiftDays as float
DECLARE @OneYear as float
DECLARE @LeftDays as float
DECLARE @YearDay as Integer
DECLARE @Farsi_Date as Varchar(100)
IF @MDate='2010-03-20'
Return '1388/12/29'
IF @MDate='2011-03-20'
Return '1389/12/29'
IF @MDate='2012-03-20'
Return '1391/01/01'
IF @MDate='2013-03-20'
Return '1391/12/30'
IF @MDate='2014-03-20'
Return '1392/12/29'
IF @MDate='2015-03-20'
Return '1393/12/29'
IF @MDate='2016-03-20'
Return '1395/01/01'
SET @MDate=@MDate-CONVERT(char,@MDate,114)
SET @ShiftDays=466699 +2
SET @OneYear= 365.24199
SET @SYear = 0
SET @SMonth = 0
SET @SDay = 0
SET @AllDays = CAst(@Mdate as Real)
SET @AllDays = @AllDays + @ShiftDays
SET @SYear = (@AllDays / @OneYear) --trunc
SET @LeftDays = @AllDays - @SYear * @OneYear
if (@LeftDays < 0.5)
begin
SET @SYear=@SYear+1
SET @LeftDays = @AllDays - @SYear * @OneYear
end;
SET @YearDay = @LeftDays --trunc
if (@LeftDays - @YearDay) >= 0.5
SET @YearDay=@YearDay+1
if ((@YearDay / 31) > 6 )
begin
SET @SMonth = 6
SET @YearDay=@YearDay-(6 * 31)
SET @SMonth= @SMonth+( @YearDay / 30)
if (@YearDay % 30) <> 0
SET @SMonth=@SMonth+1
SET @YearDay=@YearDay-((@SMonth - 7) * 30)
end
else
begin
SET @SMonth = @YearDay / 31
if (@YearDay % 31) <> 0
SET @SMonth=@SMonth+1
SET @YearDay=@YearDay-((@SMonth - 1) * 31)
end
SET @SDay = @YearDay
SET @SYear=@SYear+1
SET @Farsi_Date = CAST (@SYear as VarChar(10))
IF @SMonth<10
SET @Farsi_Date = @Farsi_Date + '/0' + CAST (@SMonth as VarChar(10))
ELSE
SET @Farsi_Date = @Farsi_Date + '/' + CAST (@SMonth as VarChar(10))
IF @SDay<10
SET @Farsi_Date = @Farsi_Date + '/0' + CAST (@SDay as VarChar(10))
ELSE
SET @Farsi_Date = @Farsi_Date + '/' + CAST (@SDay as VarChar(10))
--SET @Farsi_Date = CAST (@SYear as VarChar(10)) + '/' + CAST (@SMonth as VarChar(10)) + '/' + CAST (@SDay as VarChar(10))
Return @Farsi_Date
END