Skip to content

Instantly share code, notes, and snippets.

@afruzan
Last active March 18, 2021 12:24
Show Gist options
  • Save afruzan/062d58b1b12d4366bcb273338ea37631 to your computer and use it in GitHub Desktop.
Save afruzan/062d58b1b12d4366bcb273338ea37631 to your computer and use it in GitHub Desktop.
Iran Official Date And Time (Jalali calendar - Tehran TimeZone) Solution in SQL raw and linq query (ef core 5).
declare @myutcdate as datetime2 = CAST('2021-03-17 22:00' AS datetime2);
select
FORMAT(SWITCHOFFSET(@myutcdate, '+03:30'), 'yyyy-MM-dd', 'fa') as 'get iran date',
FORMAT(SWITCHOFFSET(@myutcdate, '+03:30'), 'yyyy-MM', 'fa') as 'get iran year-month',
CAST(FORMAT(SWITCHOFFSET(@myutcdate, '+03:30'), 'dd', 'fa') as int) as 'get iran day',
CAST(FORMAT(@myutcdate, 'dd', 'fa') as int) as 'invalid time-zone results in invalid date.'
@afruzan
Copy link
Author

afruzan commented Mar 18, 2021

test performance on about 4m records

--select RecievedDateTime from Table -- 00:11

--select Format(RecievedDateTime , 'yyyy-MM-dd') from Table -- 00:44

--select dbo.GregorianToJalali(RecievedDateTime , 'yyyy-MM-dd') from Table -- 03:42

--select Format(RecievedDateTime , 'yyyy-MM-dd', 'fa') from Table -- 03:47

--select dbo.GregorianToJalali(RecievedDateTime , 'MM') from Table -- 03:35

--select Format(RecievedDateTime , 'MM', 'fa') from Table -- 01:54

--select Format(RecievedDateTime , 'yyyy-MM', 'fa') from Table -- 02:33

--select Format(SWITCHOFFSET(RecievedDateTime, '+03:30'), 'yyyy-MM', 'fa') from Table -- 02:33

--select Left(RecievedDateTime_Iran_StoredComputedColumn, '7') from Table -- 00:12 -- getting yyyy-MM

Format function is not deterministic and cannot be used in StoredComputedColumns. so for last select we should use GregorianToJalali or another way.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment