Here is a great TED talk made by American model Cameron Russell. The title ‘Looks aren’t everything. Believe me, I’m a model’, pretty much sums it all. I suggest everyone to watch it and think about it…
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:
And the result is:
Hope this helps.
You’ve probably all heard of Google PageRank. In short, it’s an algorithm used to show how important some site is, taking into account other sites linking to it on the Internet. PageRank is one of the factors Google uses when determining SERP rankings.
Although PR is less and less important in Google algorithms, it was nice seeing that this blog got its first PR! Yesterday I’ve noticed that PR update happened, since new PR for this blog is now 3. That means, we’re in the drama crowd!
It’s nice seeing that people find this blog interesting, thank you all!
This is one of hilarious Google April Fools’ Day jokes. Or maybe it’s not a joke?