Girl From Out of This World

Shamsi calendar, also known as Iranian, Persian or Jalāli Calendar is a solar calendar which is still in use in Iran and Afghanistan. If you need to convert date from Gregorian to Jalali calendar and vice versa, here is a handy Iraniar Calendar Converter. On the other hand, if you need to convert the dates in your own application, check the SQL Server function in this article.

So, let’s start. The main function is getShamsiDate, which receives Gregorian date and returns Shamsi date. I won’t get into astronomical explanations, but you can check this link to get more insight.

CREATE FUNCTION [dbo].[getShamsiDate] (@Date smalldatetime)
RETURNS nvarchar(20)
AS
BEGIN
    


DECLARE @Year int,
		@Month int,
		@Day int, 
		@ToEid int, 
		@Elapsed int, 
		@counter int,
		@retVal nvarchar(20),
		@nDay nvarchar(2),
		@nMonth nvarchar(2)

DECLARE @LeapSumOfDays TABLE 
(
	ID int,
	Val int
)

DECLARE @NonLeapSumOfDays TABLE(
	ID int,
	Val int
)

INSERT INTO @LeapSumOfDays
SELECT ROW_NUMBER() OVER (ORDER BY CAST([value] AS int)) AS ID, CAST([value] AS int) AS Val
FROM dbo.SplitStringList ('0,31,62,93,124,155,186,216,246,276,306,336,366')


INSERT INTO @NonLeapSumOfDays
SELECT ROW_NUMBER() OVER (ORDER BY CAST([value] AS int)) AS ID, CAST([value] AS int) AS Val
FROM dbo.SplitStringList ('0,31,62,93,124,155,186,216,246,276,306,336,365')


SET @Year = YEAR(@Date) - 621

IF dbo.isLeapYearShamsi(@Year - 1) = 1 AND dbo.isLeapYear(YEAR(@Date)) = 1
	SET @ToEid = 80
ELSE
	SET @ToEid = 79

IF DATEPART(dy,@Date) <= @ToEid
BEGIN
	SET @Year = @Year-1
	SET @Elapsed = 286 + DATEPART(dy,@Date)
	IF dbo.isLeapYearShamsi(@Year) = 1 AND dbo.isLeapYear(YEAR(@Date)) = 0
		SET @Elapsed = @Elapsed + 1
END
ELSE
	SET @Elapsed = DATEPART(dy,@Date) - @ToEid
     

IF dbo.isLeapYearShamsi(@Year) = 1
BEGIN
	SELECT TOP 1 @counter = ID FROM @NonLeapSumOfDays WHERE val >= @Elapsed
	SET @Month = @counter - 1
	SELECT @Day = @Elapsed - val FROM @NonLeapSumOfDays WHERE ID = @counter - 1
END
ELSE
BEGIN
	SELECT TOP 1 @counter = ID FROM @LeapSumOfDays WHERE val >= @Elapsed
	SET @Month = @counter - 1
	SELECT @Day = @Elapsed - val FROM @LeapSumOfDays WHERE ID = @counter - 1
END


IF LEN(@Day) = 1 
	SET @nDay = '0' + CAST(@Day AS nvarchar(2))
ELSE
	SET @nDay = CAST(@Day AS nvarchar(2))

IF LEN(@Month) = 1 
	SET @nMonth = '0' + CAST(@Month AS nvarchar(2))
ELSE
	SET @nMonth =  CAST(@Month AS nvarchar(2))


SET @retVal = CAST(@Year AS nvarchar(4)) + '/' + @nMonth + '/' + @nDay   

RETURN @retVal

END

There are couple of helper functions used by the main function, and their code follows.

SplitStringList function gets comma separated list of strings and returns one column table, having given string values as table records. It is useful in cases you want to get some values into the table without writing INSERT INTO statement n times.

CREATE FUNCTION [dbo].[SplitStringList]
(
	@StrList nvarchar(4000)
)
RETURNS 
@ParsedList table
(
	value nvarchar(500) COLLATE Arabic_CI_AS
)
AS
BEGIN
	DECLARE @Value nvarchar(500), @Pos int

	SET @StrList = LTRIM(RTRIM(@StrList))+ ','
	SET @Pos = CHARINDEX(',', @StrList, 1)

	IF REPLACE(@StrList, ',', '') <> ''
	BEGIN
		WHILE @Pos > 0
		BEGIN
			SET @Value = LTRIM(RTRIM(LEFT(@StrList, @Pos - 1)))
			IF @Value <> ''
			BEGIN
				INSERT INTO @ParsedList (value) 
				VALUES (CAST(@Value AS nvarchar)) --Use Appropriate conversion
			END
			SET @StrList = RIGHT(@StrList, LEN(@StrList) - @Pos)
			SET @Pos = CHARINDEX(',', @StrList, 1)

		END
	END	
	RETURN
END

Function isLeapYear checks if given year is (Gregorian)leap year. The code is really simple – it is checked if February 29th exists in the given year.

CREATE FUNCTION [dbo].[isLeapYear] (@Year SMALLINT)
RETURNS BIT
AS
BEGIN
    DECLARE @leapDate SMALLDATETIME
    DECLARE @checkDay TINYINT
 
    SET @leapDate = CONVERT(VARCHAR(4), @Year) + '0228'
    SET @checkDay = DATEPART(d, DATEADD(d, 1, @leapDate))
    IF (@checkDay = 29)
        RETURN 1

    RETURN 0  
END

isLeapYearShamsi checks if the given year is Shamsi leap year. Once more, you can check the algorithm, if you want to find more.

CREATE FUNCTION [dbo].[isLeapYearShamsi]( @Year int )
RETURNS bit
AS

BEGIN 

	DECLARE @B int,
			@isLeap bit

	SET @B = @Year % 33

	IF @B IN (1, 5, 9, 13, 17, 22, 26, 30)
		SET @isLeap = 1
	ELSE
		SET @isLeap = 0
	
	RETURN @isLeap

END

So, to get Shamsi representation of, for example, today’s date, call the function:

SELECT dbo.getShamsiDate('20130430')

And the result is:

1392/02/10

Hope this helps.