In past projects, I tried to save my time format in Gregorian dates and convert Gregorian dates to Persian in the logic layer of application, but I'm tired of doing it in that way.
I need save and restore date time with format of Persian in sql server without any conversion
Can type of datetime2 in sql server store Persian date time?
If not, what is the best way to store Persian date time?
datetime2 in SQL Server uses the Gregorian calendar.
If you want to use dates based on the Persian calendar, then you either need to make and use some functions that you can call in your SQL code to do the conversion for you, or you need to make a User Defined Type that can store the data.
Here's an example of some functions that can convert between Persian and Gregorian. I haven't tried them out so I have no idea if they work, or to their quality.
I couldn't find an example of creating a UDT for Persian dates, but this is the documentation for UDTs in general.
Personally, I would store the dates in SQL Server as UTC Gregorian
datetime2. And I'd either have conversion code in the application data layer that converts them when saving to, and loading from SQL Server, or I'd do the conversion when the
DateTime is displayed to/retrieved from the user.
UTC is the date and time standard for the world, especially for science and engineering, and as such SQL Server and .Net have been built using it. It makes sense to also use UTC so that you can get the most benefit from the existing SQL Server and .Net code. Of course your users still want to be able to write and read a
DateTime using the Persian calendar, so it makes sense to me that you'd perform that conversion just before one will be displayed to the user, or just after the user has provided one.
As this page says, you can use below functions to convert calendar to persian and vice versa.
Create FUNCTION [dbo].[UDF_Persian_To_Julian](@iYear int,@iMonth int,@iDay int) RETURNS bigint AS Begin Declare @PERSIAN_EPOCH as int Declare @epbase as bigint Declare @epyear as bigint Declare @mdays as bigint Declare @Jofst as Numeric(18,2) Declare @jdn bigint Set @PERSIAN_EPOCH=1948321 Set @Jofst=2415020.5 If @iYear>=0 Begin Set @[email protected] End Else Begin Set @epbase = @iYear - 473 End set @epyear=474 + (@epbase%2820) If @iMonth<=7 Begin Set @mdays=(Convert(bigint,(@iMonth) - 1) * 31) End Else Begin Set @mdays=(Convert(bigint,(@iMonth) - 1) * 30+6) End Set @jdn =Convert(int,@iday) + @mdays+ Cast(((@epyear * 682) - 110) / 2816 as int) + (@epyear - 1) * 365 + Cast(@epbase / 2820 as int) * 1029983 + (@PERSIAN_EPOCH - 1) RETURN @jdn End
and the second function is:
Create FUNCTION [dbo].[UDF_Julian_To_Gregorian] (@jdn bigint) Returns nvarchar(11) as Begin Declare @Jofst as Numeric(18,2) Set @Jofst=2415020.5 Return Convert(nvarchar(11),Convert(datetime,(@jdn- @Jofst),113),110) End
the last function is:
Create Function dbo.[UDF_Gregorian_To_Persian] (@date datetime) Returns nvarchar(50) as Begin Declare @depoch as bigint Declare @cycle as bigint Declare @cyear as bigint Declare @ycycle as bigint Declare @aux1 as bigint Declare @aux2 as bigint Declare @yday as bigint Declare @Jofst as Numeric(18,2) Declare @jdn bigint Declare @iYear As Integer Declare @iMonth As Integer Declare @iDay As Integer Set @Jofst=2415020.5 Set @jdn=Round(Cast(@date as int)+ @Jofst,0) Set @depoch = @jdn - [dbo].[UDF_Persian_To_Julian](475, 1, 1) Set @cycle = Cast(@depoch / 1029983 as int) Set @cyear = @depoch%1029983 If @cyear = 1029982 Begin Set @ycycle = 2820 End Else Begin Set @aux1 = Cast(@cyear / 366 as int) Set @aux2 = @cyear%366 Set @ycycle = Cast(((2134 * @aux1) + (2816 * @aux2) + 2815) / 1028522 as int) + @aux1 + 1 End Set @iYear = @ycycle + (2820 * @cycle) + 474 If @iYear <= 0 Begin Set @iYear = @iYear - 1 End Set @yday = (@jdn - [dbo].[UDF_Persian_To_Julian](@iYear, 1, 1)) + 1 If @yday <= 186 Begin Set @iMonth = CEILING(Convert(Numeric(18,4),@yday) / 31) End Else Begin Set @iMonth = CEILING((Convert(Numeric(18,4),@yday) - 6) / 30) End Set @iDay = (@jdn - [dbo].[UDF_Persian_To_Julian](@iYear, @iMonth, 1)) + 1 Return Convert(nvarchar(50),@iYear) + '/' + RIGHT('0' + CAST(@iMonth AS VARCHAR(2)), 2) +'/' + RIGHT('0' + CAST(@iDay AS VARCHAR(2)), 2)
Example of using this function is:
SELECT [dbo].[UDF_Gregorian_To_Persian] ('2018-2-5') as PersianDate