Iran Official Date And Time (Jalali calendar - Tehran TimeZone) Solution in SQL raw and linq query (ef core 5).

IranOfficialDateAndTimeInSQLServer.sql

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.'

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

1. Add custom/built-in functions in a static class

    public static class CustomFunctions
    {
        public static DateTime ChangeTimeZoneOffset(this DateTime datetime, string timeZoneOffset)
        {
            throw new NotSupportedException();
        }
        public static DateTime ChangeTimeZoneOffset(this DateTime datetime, int timeZoneOffset)
        {
            throw new NotSupportedException();
        }
        public static string Format(this DateTime datetime, string format)
        {
            throw new NotSupportedException();
        }
        public static string Format(this DateTime datetime, string format, string culture)
        {
            throw new NotSupportedException();
        }
    }

2. Map those functions correctly:

modelBuilder.HasDbFunction(typeof(CustomFunctions).GetMethod(nameof(CustomFunctions.ChangeTimeZoneOffset), new[] { typeof(DateTime), typeof(string) }))
                .HasName("SwitchOffset").IsBuiltIn();

modelBuilder.HasDbFunction(typeof(CustomFunctions).GetMethod(nameof(CustomFunctions.ChangeTimeZoneOffset), new[] { typeof(DateTime), typeof(int) }))
                .HasName("SwitchOffset").IsBuiltIn();

modelBuilder.HasDbFunction(typeof(CustomFunctions).GetMethod(nameof(CustomFunctions.Format), new[] { typeof(DateTime), typeof(string) }))
                .HasName("Format").IsBuiltIn();

modelBuilder.HasDbFunction(typeof(CustomFunctions).GetMethod(nameof(CustomFunctions.Format), new[] { typeof(DateTime), typeof(string), typeof(string) }))
                .HasName("Format").IsBuiltIn();

3. Use it in your LINQ:

    ...
    .Select(i => new
    {
        TheDateTimeInUserTimeZone = CustomFunctions.ChangeTimeZoneOffset(i.MyDateTime, timeZoneOffsetVariable)
    }

---

If your time zone offset is static (eq your user country is static but datetimes are stored in UTC), One solution is using Computed Column:

entity.Property(e => e.TheDateTimeInIRIran).HasComputedColumnSql("(SWITCHOFFSET([MyDateTime], '+03:30'))", false);

also you can convert date from greg to jalali for example:

entity.Property(e => e.TheDateTimeInIRIran).HasComputedColumnSql("(FORMAT(SWITCHOFFSET([MyDateTime], '+03:30'), 'yyyy/MM/dd HH:mm:ss', 'fa-ir'))", false);

or use At Time Zone clause (SQL Server 2016+) supporting daylight saving time.

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.