تابع زیر در SQL ، رشته تاریخ شمسی رو میگیره و تاریخ میلادی از نوع DateTime بر میگردونه
CREATE FUNCTION [dbo].[ShamsiToMiladi] ( @DateStr VARCHAR(10) )
RETURNS DATETIME
AS
BEGIN
DECLARE @YYear INT
DECLARE @MMonth INT
DECLARE @DDay INT
DECLARE @epbase INT
DECLARE @epyear INT
DECLARE @mdays INT
DECLARE @persian_jdn INT
DECLARE @i INT
DECLARE @j INT
DECLARE @l INT
DECLARE @n INT
DECLARE @TMPRESULT VARCHAR(10)
DECLARE @IsValideDate INT
DECLARE @TempStr VARCHAR(20)
DECLARE @TmpDateStr VARCHAR(10)
SET @i = CHARINDEX('/', @DateStr)
IF LEN(@DateStr) - CHARINDEX('/', @DateStr,
CHARINDEX('/', @DateStr, 1) + 1) = 4
BEGIN
SET @TmpDateStr = dbo.ReversDate(@DateStr)
IF ( ISDATE(@TmpDateStr) = 1 )
RETURN @TmpDateStr
ELSE
RETURN NULL
END
ELSE
SET @TmpDateStr = @DateStr
IF ( ( @i <> 0 )
AND ( CHARINDEX('/', @TmpDateStr) = 5 )
AND ( ISNUMERIC(REPLACE(@TmpDateStr, '/', '')) = 1 )
AND ( CHARINDEX('.', @TmpDateStr) = 0 )
)
BEGIN
SET @YYear = CAST(SUBSTRING(@TmpDateStr, 1, @i - 1) AS INT)
IF ( @YYear < 1300 )
SET @YYear = @YYear + 1300
IF @YYear > 9999
RETURN NULL
SET @TempStr = SUBSTRING(@TmpDateStr, @i + 1, LEN(@TmpDateStr))
SET @i = CHARINDEX('/', @TempStr)
SET @MMonth = CAST(SUBSTRING(@TempStr, 1, @i - 1) AS INT)
SET @MMonth = @MMonth-- -1
SET @TempStr = SUBSTRING(@TempStr, @i + 1, LEN(@TempStr))
SET @DDay = CAST(@TempStr AS INT)
SET @DDay = @DDay-- - 1
IF ( @YYear >= 0 )
SET @epbase = @YYear - 474
ELSE
SET @epbase = @YYear - 473
SET @epyear = 474 + ( @epbase % 2820 )
IF ( @MMonth <= 7 )
SET @mdays = ( ( @MMonth ) - 1 ) * 31
ELSE
SET @mdays = ( ( @MMonth ) - 1 ) * 30 + 6
SET @persian_jdn = ( @DDay ) + @mdays
+ CAST(( ( ( @epyear * 682 ) - 110 ) / 2816 ) AS INT)
+ ( @epyear - 1 ) * 365 + CAST(( @epbase / 2820 ) AS INT)
* 1029983 + ( 1948321 - 1 )
IF ( @persian_jdn > 2299160 )
BEGIN
SET @l = @persian_jdn + 68569
SET @n = CAST(( ( 4 * @l ) / 146097 ) AS INT)
SET @l = @l - CAST(( ( 146097 * @n + 3 ) / 4 ) AS INT)
SET @i = CAST(( ( 4000 * ( @l + 1 ) ) / 1461001 ) AS INT)
SET @l = @l - CAST(( ( 1461 * @i ) / 4 ) AS INT) + 31
SET @j = CAST(( ( 80 * @l ) / 2447 ) AS INT)
SET @DDay = @l - CAST(( ( 2447 * @j ) / 80 ) AS INT)
SET @l = CAST(( @j / 11 ) AS INT)
SET @MMonth = @j + 2 - 12 * @l
SET @YYear = 100 * ( @n - 49 ) + @i + @l
END
SET @TMPRESULT = CAST(@MMonth AS VARCHAR(2)) + '/'
+ CAST(@DDay AS VARCHAR(2)) + '/'
+ CAST(@YYear AS VARCHAR(4))
RETURN CAST(@TMPRESULT AS DATETIME)
END
RETURN NULL
END
GO
نحوه فراخوانی :
SELECT dbo.ShamsiToMiladi('1393/09/01')