How to save Persian date time in SQL server?

Question

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?

Answer 1

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.

Answer 2

You can use in SQL SERVER 2016 above Persian format like this function :

ALTER FUNCTION [Fun].[PersianDateFormat]
(
    @Date   datetime ,      --تاریخ میلادی ورودی 
    @How    tinyint =1      --0.numberFull and time 1.numbers   2.Month latter  3.Day latter    4.Month and Day latter  5.just Month    6.Just Day
)
RETURNS Nvarchar(30)
AS
BEGIN
    DECLARE @PersianDate Nvarchar(30)
    if @How = 0
        SELECT @PersianDate=FORMAT(@Date, 'yyyy/MM/dd-hh:mm:ss', 'fa')
    if @How = 1
        SELECT @PersianDate=FORMAT(@Date, 'yyyy/MM/dd', 'fa')
    if @How = 2
        SELECT @PersianDate=FORMAT(@Date, 'yyyy/MMM/dd', 'fa')
    if @how = 3
        SELECT @PersianDate=FORMAT(@Date, 'yyyy/MM/ddd', 'fa')      
    if @how = 4
        SELECT @PersianDate=FORMAT(@Date, 'yyyy/MMM/ddd', 'fa') 
    if @how = 5
        SELECT @PersianDate=FORMAT(@Date, 'MMM', 'fa')  
    if @how = 6
        SELECT @PersianDate=FORMAT(@Date, 'ddd', 'fa')  
    RETURN @PersianDate

END

You can use directly in query for change date to Persian like this :

Select FORMAT(Getdate(), 'yyyy/MM/dd-hh:mm:ss', 'fa')

I hope you wish the best

Answer 3

You can use the 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 @epbase=@iyear-474 
    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_Gregorian_To_Persian] (@date datetime)
Returns NvarChar(10) AS
begin

Declare @gd As int
Declare @gm As int
Declare @gm2 As int
Declare @gy As int
Declare @gy2 As int
Declare @jy as int
Declare @jd as int
Declare @jm as int
Declare @days as int
Declare @g_d_m as int

Declare @Result As NVarChar(10)

set @gy = YEAR(@date)
set @gm = MONTH(@date)
set @gd = DAY(@date)

if @gy <= 1600 
    set @jy = 0
else
    set @jy = 979

if @gy <= 1600 
    set @gy = @gy - 621
else
    set @gy = @gy - 1600

if @gm > 2 
    set @gy2 = @gy + 1
else
    set @gy2 = @gy

set @gm2 = @gm - 1

if @gm2 = 0
    set @g_d_m = 0
else if @gm2 = 1
    set @g_d_m = 31
else if @gm2 = 2
    set @g_d_m = 59
else if @gm2 = 3
    set @g_d_m = 90
else if @gm2 = 4
    set @g_d_m = 120
else if @gm2 = 5
    set @g_d_m = 151
else if @gm2 = 6
    set @g_d_m = 181
else if @gm2 = 7
    set @g_d_m = 212
else if @gm2 = 8
    set @g_d_m = 243
else if @gm2 = 9
    set @g_d_m = 273
else if @gm2 = 10
    set @g_d_m = 304
else if @gm2 = 11
    set @g_d_m = 334

set @days = (365 * @gy) + ((@gy2 + 3) / 4) - ((@gy2 + 99) / 100) + ((@gy2 + 399) / 400) - 80 + @gd + @g_d_m 
set @jy = @jy +  33 * (@days / 12053);
set @days = @days % 12053
set @jy = @jy + 4 * (@days / 1461)
set @days = @days % 1461
set @jy = @jy + ((@days - 1) / 365);

if @days > 365
    set @days = (@days - 1) % 365;

if @days < 186
    set @jm = 1 + (@days / 31) 
else
    set @jm = 7 + ((@days - 186) / 30);

if @days < 186
    set @jd = 1 + (@days % 31) 
else
    set @jd = 1 + ((@days - 186) % 30)

Return Convert(nvarchar(50),@jy) + '/' + RIGHT('0' + CAST(@jm AS VARCHAR(2)), 2) +'/' + RIGHT('0' + CAST(@jd AS VARCHAR(2)), 2)
end

Example of using this function is:

SELECT [dbo].[UDF_Gregorian_To_Persian] ('2018-2-5') as PersianDate

Result is:

1396/11/16