Convert Hijri Shamsi date format tsql

Question

I want convert my Hijri Shamsi date '92/2/3' to this format : '92/02/03'

With this code, I get this error :The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Declare @Str Varchar(10) = '92/2/3'
Select Convert(Varchar(10), Convert(DateTime, @Str), 111)

How can i change format of date ?

Answer 1

1. Since you don't have centuries you should use 11, not 111 as your conversion code

2. You need to use 11 in the inner convert to tell it what format it is converting FROM

   Declare @Str Varchar(10) = '92/2/3'    
   Select Convert(Varchar(10), Convert(DateTime, @Str,11), 11)
   

Answer 2

Use this functions to convert Gregorian date to Hijri(Shamsi) date with your requested format:

CREATE FUNCTION [dbo].[ShamsiDate]
        (
          @ChirsDate SMALLDATETIME
        )
    RETURNS CHAR(10)
    AS
        BEGIN
            DECLARE @SolarDate CHAR(10)
            DECLARE @Day CHAR(2)
            DECLARE @Mon CHAR(2)
            DECLARE @SDay INT
            DECLARE @SMon INT
            DECLARE @SYear INT
            SET @SYear = dbo.ShamsiDatePart(@ChirsDate, 'Y')
            SET @SMon = dbo.ShamsiDatePart(@ChirsDate, 'M')
            SET @SDay = dbo.ShamsiDatePart(@ChirsDate, 'D')
            IF @SMon <= 9
                SELECT  @Mon = '0' + CONVERT(CHAR(1), @SMon)
            ELSE
                SELECT  @Mon = CONVERT(CHAR(2), @SMon)
            IF @SDay <= 9
                SELECT  @Day = '0' + CONVERT(CHAR(1), @SDay)
            ELSE
                SELECT  @Day = CONVERT(CHAR(2), @SDay)
            SELECT  @SolarDate = CONVERT(CHAR(4), @SYear) + '/' + @Mon + '/'
                    + @Day
            RETURN @SolarDate
        END

    CREATE FUNCTION [dbo].[ShamsiDatePart]
        (
          @MiDate DATETIME ,
          @ADatePart CHAR
        )
    RETURNS INT
    AS
        BEGIN
            DECLARE @TmpY INT ,
                @Leap INT
            DECLARE @Sh_Y INT ,
                @Sh_M INT ,
                @Sh_D INT ,
                @Result INT
            IF @MiDate IS NULL
                RETURN 0
            DECLARE @Result INT
            SET @Result = CONVERT(INT, CONVERT(FLOAT, @MiDate))
            IF @Result <= 78
                BEGIN
                    SET @Sh_Y = 1278
                    SET @Sh_M = ( @Result + 10 ) / 30 + 10
                    SET @Sh_D = ( @Result + 10 ) % 30 + 1
                END
            ELSE
                BEGIN
                    SET @Result = @Result - 78
                    SET @Sh_Y = 1279
                    WHILE 1 = 1
                        BEGIN
                            SET @TmpY = @Sh_Y + 11
                            SET @TmpY = @TmpY - ( @TmpY / 33 ) * 33
                            IF ( @TmpY <> 32 )
                                AND ( ( @TmpY / 4 ) * 4 = @TmpY )
                                SET @Leap = 1
                            ELSE
                                SET @Leap = 0
                            IF @Result <= ( 365 + @Leap )
                                BREAK
                            SET @Result = @Result - ( 365 + @Leap )
                            SET @Sh_Y = @Sh_Y + 1
                        END
                    IF @Result <= 31 * 6
                        BEGIN
                            SET @Sh_M = ( @Result - 1 ) / 31 + 1
                            SET @Sh_D = ( @Result - 1 ) % 31 + 1
                        END
                    ELSE
                        BEGIN
                            SET @Sh_M = ( ( @Result - 1 ) - 31 * 6 ) / 30 + 7
                            SET @Sh_D = ( ( @Result - 1 ) - 31 * 6 ) % 30 + 1
                        END
                END
            RETURN CASE @ADatePart WHEN 'Y' THEN @Sh_Y WHEN 'M' THEN @Sh_M WHEN 'D' THEN @Sh_D ELSE 0 END
        END