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)

This is very well documented in the [SO Documentation](http://stackoverflow.com/documentation/sql-server/1471/dates#t=201703141241406432461) — Siyual, Mar 14 '17 at 12:42
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. — Damien_The_Unbeliever, Mar 14 '17 at 12:42
Depending on your SQL Server version (tag it please), you might be able to use Format — Nick.Mc, Mar 14 '17 at 13:21
The usual thing to do now is post your solution so that everyone can benefit, or at least give the answers below some closure. — Nick.Mc, Mar 15 '17 at 12:48

score 3 · Answer 1 · edited Mar 14 '17 at 14:33

3

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

edited Mar 14 '17 at 14:33

Venu

455
2
7

answered Mar 14 '17 at 12:43

Gordon Linoff

1,242,037
58
646
786

Do you not have to reverse the day/month elements also? OP has requested 1438/06/16, your code produces 1438/16/06 – Damien_The_Unbeliever Mar 14 '17 at 12:47
Thank you very much, your script just did the magic @GordonLinoff – Hussein ALSHAMIRI Mar 14 '17 at 15:39
1

@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. – Damien_The_Unbeliever Mar 15 '17 at 15:10
@Damien_The_Unbeliever I see now! answer removed – Matt Mar 15 '17 at 22:08
Here you go @Nick.McDermaid – Hussein ALSHAMIRI Mar 18 '17 at 11:34

Venu · Answer 2 · 2017-03-14T13:12:48.007

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

edited Mar 14 '17 at 13:12

answered Mar 14 '17 at 12:44

Venu

455
2
7

score 1 · Answer 3 · edited Mar 14 '17 at 15:08

1

Use below query :

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

edited Mar 14 '17 at 15:08

KristofMols

3,487
2
38
48

answered Mar 14 '17 at 12:42

Mansoor

4,061
1
17
27

The ‍‍‍```FORMAT``` function was introduced with ```SQL Server 2012``` and as pointed in the docs it is available in all supported versions. – Sina Jul 17 '23 at 10:09

score 1 · Answer 4 · answered Mar 14 '17 at 13:03

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   |
+--------------+--------------+

score 0 · Answer 5 · answered Mar 14 '17 at 12:41

0

Try with 120:

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

answered Mar 14 '17 at 12:41

Carlos

1,638
5
21
39

score 0 · Answer 6 · edited Mar 14 '17 at 15:10

0

You can do that using the following query:

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

edited Mar 14 '17 at 15:10

KristofMols

3,487
2
38
48

answered Mar 14 '17 at 12:46

udhaya kumar

161
6

Converting GETDATE() to Hijri date to yyyymmdd

6 Answers6