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)

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