I want to convert my datetime into Persian datetime in SQL Server. My datetime is in MM/DD/YYYY format. Is there any function in SQL Server to do this as when I want hijri datetime I use this
SELECT CONVERT(VARCHAR(40), GETDATE(), 131) -- Output is 14/08/1432 5:02:01:197PM
I'm using SQL Server 2008.
Best method in SQL Server 2016
Example:
SELECT FORMAT(GETDATE(), 'yyyy/MM/dd-HH:mm:ss', 'fa')
Answer:
1398/10/08-05:37:59
TimeZone of the hour and minute are wrong. I was able to fix it like this: SELECT FORMAT(sysdatetimeoffset() at time zone 'Iran Standard Time' , 'yyyy/MM/dd-HH:mm:ss', 'fa'). I used sysdatetimeoffset() instead of getdate() and usded the at time zone part –
Margeret I know it is too late for answering this question, but I've submitted the function that I'm using for a long time without any bug, all of other methods which I've ever seen have problem with intercalary years:
CREATE FUNCTION [CalculatePersianDate] ( @intDate DATETIME )
RETURNS NVARCHAR(max)
BEGIN
DECLARE @shYear AS INT ,@shMonth AS INT ,@shDay AS INT ,@intYY AS INT ,@intMM AS INT ,@intDD AS INT ,@Kabiseh1 AS INT ,@Kabiseh2 AS INT ,@d1 AS INT ,@m1 AS INT, @shMaah AS NVARCHAR(max),@shRooz AS NVARCHAR(max),@DayCnt AS INT
DECLARE @DayDate AS NVARCHAR(max)
SET @intYY = DATEPART(yyyy, @intDate)
IF @intYY < 1000 SET @intYY = @intYY + 2000
SET @intMM = MONTH(@intDate)
SET @intDD = DAY(@intDate)
SET @shYear = @intYY - 622
SET @DayCnt = 5
IF ( ( @intYY - 1992 ) % 4 = 0) SET @Kabiseh1 = 0 ELSE SET @Kabiseh1 = 1
IF ( ( @shYear - 1371 ) % 4 = 0) SET @Kabiseh2 = 0 ELSE SET @Kabiseh2 = 1
SET @m1 = 1
SET @d1 = 1
SET @shMonth = 10
SET @shDay = 11
IF ( ( @intYY - 1993 ) % 4 = 0 ) SET @shDay = 12
WHILE ( @m1 != @intMM ) OR ( @d1 != @intDD )
BEGIN
SET @d1 = @d1 + 1
SET @DayCnt = @DayCnt + 1
IF
(@d1 = 32 AND (@m1 = 1 OR @m1 = 3 OR @m1 = 5 OR @m1 = 7 OR @m1 = 8 OR @m1 = 10 OR @m1 = 12))
OR
(@d1 = 31 AND (@m1 = 4 OR @m1 = 6 OR @m1 = 9 OR @m1 = 11))
OR
(@d1 = 30 AND @m1 = 2 AND @Kabiseh1 = 1)
OR
(@d1 = 29 AND @m1 = 2 AND @Kabiseh1 = 0)
BEGIN
SET @m1 = @m1 + 1
SET @d1 = 1
END
IF @m1 > 12
BEGIN
SET @intYY = @intYY + 1
SET @m1 = 1
END
IF @DayCnt > 7 SET @DayCnt = 1
SET @shDay = @shDay + 1
IF
(@shDay = 32 AND @shMonth < 7)
OR
(@shDay = 31 AND @shMonth > 6 AND @shMonth < 12)
OR
(@shDay = 31 AND @shMonth = 12 AND @Kabiseh2 = 1)
OR
(@shDay = 30 AND @shMonth = 12 AND @Kabiseh2 = 0)
BEGIN
SET @shMonth = @shMonth + 1
SET @shDay = 1
END
IF @shMonth > 12
BEGIN
SET @shYear = @shYear + 1
SET @shMonth = 1
END
END
IF @shMonth=1 SET @shMaah=N'فروردین'
IF @shMonth=2 SET @shMaah=N'اردیبهشت'
IF @shMonth=3 SET @shMaah=N'خرداد'
IF @shMonth=4 SET @shMaah=N'تیر'
IF @shMonth=5 SET @shMaah=N'مرداد'
IF @shMonth=6 SET @shMaah=N'شهریور'
IF @shMonth=7 SET @shMaah=N'مهر'
IF @shMonth=8 SET @shMaah=N'آبان'
IF @shMonth=9 SET @shMaah=N'آذر'
IF @shMonth=10 SET @shMaah=N'دی'
IF @shMonth=11 SET @shMaah=N'بهمن'
IF @shMonth=12 SET @shMaah=N'اسفند'
IF @DayCnt=1 SET @shRooz=N'شنبه'
IF @DayCnt=2 SET @shRooz=N'یکشنبه'
IF @DayCnt=3 SET @shRooz=N'دوشنبه'
IF @DayCnt=4 SET @shRooz=N'سهشنبه'
IF @DayCnt=5 SET @shRooz=N'چهارشنبه'
IF @DayCnt=6 SET @shRooz=N'پنجشنبه'
IF @DayCnt=7 SET @shRooz=N'جمعه'
--SET @DayDate = @shRooz + " " + LTRIM(STR(@shDay,2)) + " " + @shMaah + " " + STR(@shYear,4)
--پنجشنبه 17 اردیبهشت 1394
/*
SET @DayDate = LTRIM(STR(@shDay,2)) + " " + @shMaah + " " + STR(@shYear,4)
--17 اردیبهشت 1394
SET @DayDate = STR(@shYear,4) + "/"+LTRIM(STR(@shMonth,2)) + "/" + LTRIM(STR(@shDay,2))
--1394/2/17
--1394/02/17
*/
SET @DayDate = REPLACE(RIGHT(STR(@shYear, 4), 4), ' ', '0') + '/'+ REPLACE(STR(@shMonth, 2), ' ', '0') + '/' + REPLACE(( STR(@shDay,2) ), ' ', '0')
RETURN @DayDate
END
It is really easy to customize the result of the function. adopted from: this page
I know it is too late but maybe useful for others like me having this trouble.
You should write a SQL Function for this conversion like this: Converting Gregorian to Persian Date
and then use it like this:
SELECT dbo.[UDF_Gregorian_To_Persian]('2013-08-24')
Try this:
select format(getdate() , 'yyyy/MM/dd', 'fa-ir')
You can use the following code to convert the date. This practical and important method has been added to the 2012 version of SQL and can be used.
SELECT FORMAT(GETDATE(), 'yyyy/MM/dd-HH:mm:ss', 'fa')
Result: 1400/02/08-05:08:51
SELECT cast( FORMAT(GETDATE(), 'yyyyMMdd', 'fa') as int)
Result: 14000208
And you can use Format as Follow to get Higri Date:
SELECT FORMAT(GETDATE(), N'yyyy/MM/dd', N'ar')
Result: 1443/06/19
I believe the best available solution is to use SQLCLR-Jalali-Date-Utility. It has a straightforward installation guide and easy to use functions. Moreover, you can define the format of the converted date without any limitation. in fact, you can use the standard time formatting to define the shape of converted dates.
There are several examples provided inside the GitHub page.
select dbo.GregorianToJalali(GETDATE(),'yyyy/MM/dd hh:mm:ss tt') -- returns 1395/07/01 03:04:33 ب ظ
You can convert it to shamsi using this functions.
The first function.
CREATE FUNCTION [dbo].[ToPersianDate](@dt [datetime])
RETURNS [nvarchar](10) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [PersianSQLFunctions].[UserDefinedFunctions].[ToPersianDate]
GO
second function.
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[ToPersianDate](@dt [datetime])
RETURNS [nvarchar](10) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [PersianSQLFunctions].[UserDefinedFunctions].[ToPersianDate]
GO
third function.
CREATE FUNCTION [dbo].[fnToShamsiDate]
(
@d DateTime
)
RETURNS NVARCHAR(10)
AS
BEGIN
-- Declare the return variable here
DECLARE @RV NVARCHAR(10)
IF (@d) IS NULL RETURN NULL;
ELSE SELECT @RV = DBO.ToPersianDate(@D);
-- Return the result of the function
RETURN @RV
END
GO
And also you can find shamsi months from this function
create function [dbo].[fnGetShamsiMonth]
(
@GregorianDate date
)
returns nvarchar(2)
as
begin
declare @ShamsiMonth nvarchar(2), @ShamsiDate nvarchar(10);
set @ShamsiDate = confsys.dbo.fnToShamsiDate(@GregorianDate);
set @ShamsiMonth = SUBSTRING(@ShamsiDate,6,2);
return @ShamsiMonth
end
GO
examples
select confsys.dbo.fnToShamsiDate(getdate())
result is 1397/12/29
get shamsi months
select confsys.dbo.fnGetShamsiMonth(GETDATE());
this is persian Calendar function in SQL 2016+
ALTER FUNCTION [dbo].[PCalendar](@date datetime)
RETURNS @ret TABLE (
ly int,
y int,
m int,
mname nvarchar(15),
d int,
dy int,
dw int,
dname nvarchar(10),
hh int,
mm int,
ss int,
mss int,
dt datetime,
t nvarchar(3))
as
BEGIN
DECLARE @format varchar(19);
set @format = 'yyyy/MM/dd HH:mm:ss';
DECLARE @y int;
DECLARE @m int;
DECLARE @d int;
DECLARE @dy int;
DECLARE @dw int;
DECLARE @hh int;
DECLARE @mm int;
DECLARE @ss int;
DECLARE @ms int;
DECLARE @ldt varchar(8);
set @y = DATEPART(YEAR, FORMAT(@date, @format, 'fa')) ;
set @m = DATEPART(MONTH, FORMAT(@date, @format, 'fa'));
set @d = DATEPART(DAY, FORMAT(@date, @format, 'fa')) ;
set @dy = DATEPART(DAYOFYEAR, FORMAT(@date, @format, 'fa'));
set @dw = DATEPART(WEEKDAY, FORMAT(@date,@format, 'fa'));
set @hh = DATEPART(HOUR, @date) ;
set @mm = DATEPART(MINUTE, @date) ;
set @ss = DATEPART(SECOND, @date);
set @ms = DATEPART(MILLISECOND, @date);
set @ldt =DATEPART(year, FORMAT(@date, @format, 'en'));
DECLARE @_w nvarchar(10);
set @_w = CASE
WHEN @dw=1 THEN N'جمعه'
WHEN @dw=2 THEN N'شنبه'
WHEN @dw=3 THEN N'یکشنبه'
WHEN @dw=4 THEN N'دوشنبه'
WHEN @dw=5 THEN N'سه شنبه'
WHEN @dw=6 THEN N'چهارشنبه'
ELSE N'پنج شنبه'
END;
DECLARE @_m nvarchar(15);
set @_m = CASE
WHEN @m=1 THEN N'فروردین'
WHEN @m=2 THEN N'اردیبهشت'
WHEN @m=3 THEN N'خرداد'
WHEN @m=4 THEN N'تیر'
WHEN @m=5 THEN N'مرداد'
WHEN @m=6 THEN N'شهریور'
WHEN @m=7 THEN N'مهر'
WHEN @m=8 THEN N'آبان'
WHEN @m=9 THEN N'آذر'
WHEN @m=10 THEN N'دی'
WHEN @m=11 THEN N'بهمن'
ELSE N'اسفند'
END;
set @_m = @_m+N' ماه';
INSERT INTO @ret
SELECT
IIF(@y % 33 in (1,5,9,13,17,22,26,30) , 1 , 0) as ly,
@y as y,
@m as m,
@_m as mname,
@d as d,
@dy as dy,
@dw as dw,
@_w as dname,
@hh as hh,
@mm as mm,
@ss as ss,
@ms as mss,
@date as dt,
IIF(@hh > 12 , N'ب.ظ','ق.ظ') as t;
RETURN;
END
Function : Full Convert Persian (Shamsi / Jalali ) String to Gregorian (miladi) Datetime in sql server :
> create or ALTER Function [dbo].[Func_ShamsiToMiladi] (@Date
> Varchar(23) ) RETURNS DateTime BEGIN
> -- ==============================================================
> -- SELECT [dbo].[Func_ShamsiToMiladi] ('1356-09-20 05:35:00.000')
> --
> -- Output : '1977-12-11 02:05:00.000'
> -- ==============================================================
> -- BY: Shahrokh Vazifedan DECLARE @PersianDate Varchar(23) SET @PersianDate = @Date
>
> DECLARE @Year INT = SUBSTRING(@PersianDate, 1, 4)
> DECLARE @Month INT = SUBSTRING(@PersianDate, 6, 2)
> DECLARE @Day INT = SUBSTRING(@PersianDate, 9, 2)
> DECLARE @DiffYear INT = @Year - 1350
> DECLARE @Time varchar(13) = SUBSTRING(@PersianDate, 11, 13)
>
>
> DECLARE @Days INT = @DiffYear * 365.24 +
> CASE WHEN @Month < 7 THEN (@Month - 1) * 31
> ELSE 186 + (@Month - 7) * 30 END + @Day
>
> DECLARE @StartDate DATETIME = '03/21/1971'
> DECLARE @ResultDate DATE = @StartDate + @Days
>
> DECLARE @TempDate varchar(23) = Convert( Nvarchar(10) , @ResultDate ,120) + @Time DECLARE @OffSET_First_half_in_Year
> INT; SET @OffSET_First_half_in_Year = iif( Substring(Convert(
> Nvarchar(50), @TempDate,120) ,6,16) Between '03-20 20:30' and '09-22
> 20:30' , -60 ,0)
> RETURN dateadd(MINUTE, @OffSET_First_half_in_Year + (-1)*datediff(MINUTE, getutcdate(), getdate()),@TempDate ) END
CREATE FUNCTION [dbo].[MITSH] (@MDate DateTime)
RETURNS Varchar(10)
AS
BEGIN
DECLARE @SYear as Integer
DECLARE @SMonth as Integer
DECLARE @my_mah varchar(2)
declare @my_day varchar(2)
DECLARE @SDay as Integer
DECLARE @AllDays as float
DECLARE @ShiftDays as float
DECLARE @OneYear as float
DECLARE @LeftDays as float
DECLARE @YearDay as Integer
DECLARE @Farsi_Date as Varchar(100)
SET @MDate=@MDate-CONVERT(char,@MDate,114)
SET @ShiftDays=466699 +2
SET @OneYear= 365.24199
SET @SYear = 0
SET @SMonth = 0
SET @SDay = 0
SET @AllDays = CAst(@Mdate as Real)
SET @AllDays = @AllDays + @ShiftDays
SET @SYear = (@AllDays / @OneYear) --trunc
SET @LeftDays = @AllDays - @SYear * @OneYear
if (@LeftDays < 0.5)
begin
SET @SYear=@SYear+1
SET @LeftDays = @AllDays - @SYear * @OneYear
end;
SET @YearDay = @LeftDays --trunc
if (@LeftDays - @YearDay) >= 0.5
SET @YearDay=@YearDay+1
if ((@YearDay / 31) > 6 )
begin
SET @SMonth = 6
SET @YearDay=@YearDay-(6 * 31)
SET @SMonth= @SMonth+( @YearDay / 30)
if (@YearDay % 30) <> 0
SET @SMonth=@SMonth+1
SET @YearDay=@YearDay-((@SMonth - 7) * 30)
end
else
begin
SET @SMonth = @YearDay / 31
if (@YearDay % 31) <> 0
SET @SMonth=@SMonth+1
SET @YearDay=@YearDay-((@SMonth - 1) * 31)
end
SET @SDay = @YearDay
SET @SYear=@SYear+1
if @SMonth <10 begin
set @my_mah='0'+str(@SMonth,1)
end else begin
set @my_mah = str(@SMonth,2)
end
if @sday <10 begin
set @my_day='0'+str(@Sday,1)
end else begin
set @my_day = str(@Sday,2)
end
SET @Farsi_Date = CAST (@SYear as VarChar(10)) + '/' + @my_mah + '/' + @my_day
Return @Farsi_Date
END
AN FOR EXEC FUNCTION
SELECT DBO.MITSH(GETDATE())
for example date is 2020-09-25
resualt =>>>> 1399/07/04
Complete Function For Shamsi date for SQL 2008 and 2008 R2 and below versions:
CREATE FUNCTION [dbo].[PersToJul](@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
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
GO
CREATE FUNCTION [dbo].[GrToPers] (@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].[PersToJul](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].[PersToJul](@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].[PersToJul](@iYear, @iMonth, 1)) + 1
Return Convert(nvarchar(50),@iDay) + '-' + Convert(nvarchar(50),@iMonth) +'-' + Convert(nvarchar(50),@iYear)
End
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
GO
CREATE FUNCTION [dbo].[JulToGre] (@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
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
GO
CREATE FUNCTION [dbo].[COnvertOToN](@StrMyNum NVARCHAR(2))
RETURNS NVARCHAR(2)
AS
BEGIN
DECLARE @MyNunInStr NVARCHAR(10)
SET @MyNunInStr = @StrMyNum
IF LEN(@MyNunInStr) < 2
BEGIN
SET @MyNunInStr = '0' + @MyNunInStr
END
RETURN @MyNunInStr
END
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
GO
-- Changing Date Format
CREATE FUNCTION [dbo].[RevDateShm](@StrDateShamsi NVARCHAR(10), @Seperator CHAR(1))
RETURNS NVARCHAR(10)
AS
BEGIN
DECLARE @StrDayOfMotn NVARCHAR(10)
DECLARE @StrMothOfYear NVARCHAR(10)
DECLARE @StrYearOfYear NVARCHAR(10)
SET @StrDayOfMotn = dbo.COnvertOToN(REPLACE(SUBSTRING(@StrDateShamsi , 1 , ((SELECT CHARINDEX('-' , @StrDateShamsi , 0)))), '-' , ''))
SET @StrMothOfYear = dbo.COnvertOToN(REPLACE(SUBSTRING(@StrDateShamsi , ((CHARINDEX('-' , @StrDateShamsi , 0) )) , 3) , '-' , ''))
SET @StrYearOfYear = RIGHT(@StrDateShamsi , 4)
return (@StrYearOfYear + @Seperator + @StrMothOfYear + @Seperator + @StrDayOfMotn)
END
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
GO
CREATE FUNCTION [dbo].[ConvertShamsiToMiladiDate](@InputShamsiDateString nvarchar(10))
RETURNS datetime
AS
BEGIN
declare @InputShamsiDateString1 nvarchar(10)
declare @yearm int
declare @monthm int
declare @daym int
set @yearm = CONVERT(int , SUBSTRING(@InputShamsiDateString , 1 , 4))
set @monthm = CONVERT(int , SUBSTRING(@InputShamsiDateString , 6 , 2))
set @daym = CONVERT(int , SUBSTRING(@InputShamsiDateString , 9 , 2))
return (select dbo.[JulToGre](dbo.[PersToJul](@yearm,@monthm ,@daym )))
END
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
GO
-- The Latest And Main Function
CREATE FUNCTION [dbo].[GetShmsiDate](@InputMiladiDate DateTime , @MySeperatorChar char(1))
RETURNS NVARCHAR(10)
AS
BEGIN
return (select dbo.RevDateShm(dbo.GrToPers(@InputMiladiDate), @MySeperatorChar) AS ShamsiDateOfLog)
END
GO
How to use:
SELECT dbo.GetShmsiDate(GETDATE() , N'/') AS ShamsiDate1,
dbo.GetShmsiDate(GETDATE() , N'-') AS ShamsiDate2
Result:
|ShamsiDate1|ShamsiDate2|
|-----------|-----------|
|1400/11/03 | 1400-11-03|
epbase stand for? what about epyear? –
Concourse @iYear in the function [dbo].[PersToJul]). The calculations involving @epbase help determine the Persian year. –
Obscurantism @epbase value and other calculations in the [dbo].[PersToJul] function. Both variables are internal to the function and are used for intermediate calculations related to converting dates between the Gregorian and Persian calendars. –
Obscurantism To convert a date to persian, try this code:
DECLARE @DateString NVARCHAR(200)='2022/09/07';
SELECT FORMAT(CAST(@DateString AS DATE),'yyyy/MM/dd','fa');
Whenever working with Jalali dates in SQL, I use Zoghal and Jalaliware for date conversion and using Persian characters. One of the functions described in their documentation is as follows: PDATE(datetime) Takes georgian datetime as input and returns jalali date in text format.
© 2022 - 2025 — McMap. All rights reserved.