Converting GETDATE() to Hijri date to yyyymmdd

Question

I am trying to get the Hijri GETDATE() and convert it into this format yyyymmdd

I have already tried this script

SELECT CONVERT(VARCHAR(10), GETDATE(), 131)  

but it gives me this format ( 16/06/1438 ) and what I actually need is (1438/06/16)

Eloisaeloise

This is very well documented in the SO Documentation – Frontispiece

There's no built in conversion to get what you're asking for - you'd need to assemble it yourself using lots of string mangling - or, if possible, do this formatting in another layer that hopefully has better calendar/localization/formatting tools available than those found in T-SQL. – Mitigate

Depending on your SQL Server version (tag it please), you might be able to use Format – Diphyodont

It's already solved and I'm using SQL2012 @Nick.McDermaid – Eloisaeloise

The usual thing to do now is post your solution so that everyone can benefit, or at least give the answers below some closure. – Diphyodont

Answer 1

SQL Server does not offer a wealth of formatting options for such dates, so just construct it yourself:

SELECT (RIGHT(CONVERT(VARCHAR(10), GETDATE(), 131), 4) + '/' +
        CONVERT(VARCHAR(5), GETDATE(), 131)
       ) as hj_yyyymmdd

Oops. Right idea, wrong implementation:

SELECT (RIGHT(CONVERT(VARCHAR(10), GETDATE(), 131), 4) +
        SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 131), 3, 4) +
        LEFT(CONVERT(VARCHAR(10), GETDATE(), 131), 2)
   ) AS hj_yyyymmdd

Boroughenglish

Do you not have to reverse the day/month elements also? OP has requested 1438/06/16, your code produces 1438/16/06 – Mitigate

Thank you very much, your script just did the magic @GordonLinoff – Eloisaeloise

@Matt - as with many of the other answers here, you're missing the key point - the conversion needs to give the date in the Hijri calendar, not the Gregorian one. – Mitigate

@Mitigate I see now! answer removed – Gullet

Here you go @Nick.McDermaid – Eloisaeloise

Answer 2

If you are having SQL Server 2012 and above,

SELECT FORMAT(GETDATE()+1,'yyyy/MM/dd','ar')

It will give you the below result for the date 2017/03/14

1438/06/16

Popularly

Answer 3

Use below query :

SELECT FORMAT ( GETDATE(), 'yyyy/MM/dd', 'ar-SA' )

Rodney

The ‍‍‍FORMAT function was introduced with SQL Server 2012 and as pointed in the docs it is available in all supported versions. – Grandfather

Answer 4

Another option is to convert to varchar then date then varchar again.

Using format() with 'ar-SA' seems to return 1 day earlier than using convert() with style 131.

select Method='multiconvert'
  ,conversion = convert(varchar(10)
    ,convert(date,convert(varchar(12),getdate(),131),103),112)
union all 
select 'format'
  , format ( getdate(), 'yyyyMMdd', 'ar-SA' )
union all
select 'style131'
  ,convert(varchar(12),getdate(),131)

rextester demo: http://rextester.com/LIX82417

returns

+--------------+--------------+
|    Method    |  conversion  |
+--------------+--------------+
| multiconvert | 14380616     |
| format       | 14380615     |
| style131     | 16/06/1438   |
+--------------+--------------+

Chromatin

Answer 5

Try with 120:

SELECT CONVERT(VARCHAR(10), GETDATE(), 120) 

Wordsworth

Answer 6

You can do that using the following query:

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120),'-','/')

Hydric