sql-servert-sqlsql-server-2008datetime-format

Reputation: 10448

How to convert datetime in Persian in SQL Server

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.

Upvotes: 36

Answers (16)

Shahrokh Vazifedan

Reputation: 11

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' -- ============================================================== -- Shamsi to Miladi (Change Date Fromat) -- BY: Shahrokh Vazifedan [email protected] 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

How to Use:
SELECT [dbo].[Func_ShamsiToMiladi] ('1356-09-20 05:35:00.000')

Upvotes: 0

m.mirahmadi

Reputation: 1

to convert try this code :

 DECLARE @today DATE = Getdate();
 SELECT Format(@today, 'yyyy-MM-dd', 'fa-IR')

it is available in sql 2016 and later

Upvotes: 0

Shahrokh Vazifedan

Reputation: 11

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

Upvotes: 1

okaeiz

Reputation: 400

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.

Upvotes: 0

MB_18

Reputation: 2311

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');

Upvotes: 1

sohrab asadzadeh

Reputation: 81

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

Upvotes: 8

AliNajafZadeh

Reputation: 1328

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|

Upvotes: 0

F.Rhman

Reputation: 21

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());

Upvotes: 1

Hamid Habibi

Reputation: 809

Best method in SQL Server 2016

Example:

SELECT FORMAT(GETDATE(), 'yyyy/MM/dd-HH:mm:ss', 'fa')

Answer:

1398/10/08-05:37:59

Upvotes: 70

Majid Sabzalian

Reputation: 143

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

Upvotes: 1

Shahriar Khazaei

Reputation: 357

Try this:

select format(getdate() , 'yyyy/MM/dd', 'fa-ir')

Upvotes: 13

jamaljaj

Reputation: 27

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

Upvotes: 0

Ehsan Mirsaeedi

Reputation: 7592

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 ب ظ

Upvotes: 3

Saman Gholami

Reputation: 3512

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

Upvotes: 24

Bardia

Reputation: 393

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

Upvotes: 16

gbn

Reputation: 432531

Out of the box, no.

You'd have to write your own UDF, however there is one on CodePlex and another

Upvotes: 3

How to convert datetime in Persian in SQL Server

Answers (16)

RETURNS DateTime BEGIN -- ============================================================== -- SELECT [dbo].[Func_ShamsiToMiladi] ('1356-09-20 05:35:00.000')

Related Questions