How to get year of character hijri date SQL Server

Question

I searched and tried many examples unable to solve my hijri date is like,

19/07/1440

I tried this query

 SELECT TOP 200   
     DATEPART(YEAR, EndDateHejri)
 FROM 
     student

but I'm getting this error

Conversion failed when converting date and/or time from character string

I'm unable to solve error - hoping for your suggestions

What is the datatype of the `EndDateHejri` column in the `Student` table? — Mazhar, May 23 '18 at 12:10
https://raresql.com/2013/05/08/sql-server-how-to-convert-gregorian-dates-to-hijri-date-with-formatting/ — MatBailie, May 23 '18 at 12:20
https://stackoverflow.com/questions/26749403/use-sql-server-convert-function-to-convert-hijri-to-gregorian-date — MatBailie, May 23 '18 at 12:22
Try not to save the Hijri date as datetime and also try not to store the date just as UTC otherwise you must convert each datetime to Hijri by specific time zone difference and daylight time — Mohamed Azizi, May 23 '18 at 12:27
@Mat i want year part of Hijri not to convert it to georgian — syed mohsin, May 23 '18 at 12:27
i tried it by "SELECT top 200 year( convert(datetime, EndDateHejri, 131)) from student" but giving 2019 i need hijri — syed mohsin, May 23 '18 at 12:32
@syedmohsin - I know, but `DATEPART()` requires a `DATETIME` data typed parameter, and `DATETIME` is implicitly gregorian. In short, you ***can't*** use `DATEPART()` to get the year from a Hijri date. — MatBailie, May 23 '18 at 12:36

score 2 · Answer 1 · answered May 23 '18 at 12:29

I bit of Google-Fu and format 131 should help you convert Hijri dates into Gregorian Dates...

DECLARE @hijri DATETIME = CONVERT(datetime, ' 7/05/1421 12:14:35:727PM', 131)

SELECT @hijri

Unfortunately, all the date functions (DATEPART(), DATENAME(), even DATEADD(), etc) are all based on manipulating Gregorian dates. So you can't use them.

So, you're forced to use string manipulation.

DECLARE @hijri DATETIME = CONVERT(datetime, ' 7/05/1421 12:14:35:727PM', 131)

SELECT @hijri

SELECT DATEPART(year, @hijri)
-- Gives 2000 :(

SELECT RIGHT(CONVERT(VARCHAR(10), @hijri, 131), 4)
-- Gives 1421 :)

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=901209ae0cdcf38cdcdea8afad4fd034

My first thought was the right(...,4) but I wasn't sure there would be time values as well +1 — John Cappelletti, May 23 '18 at 12:36

score 2 · Answer 2 · answered May 23 '18 at 12:33

2

Posting a different answer. As the OP is only after the year part, and they've stated that it's always in the format 00/00/yyyy why not just use RIGHT? So:

SELECT RIGHT(EndDateHejri,4) as HejriYear;

answered May 23 '18 at 12:33

Thom A

88,727
11
45
75

score 2 · Answer 3 · answered Aug 19 '20 at 12:48

I tried answer @Vishnu Chandel it's working for me .

SELECT DATEPART(YEAR,CONVERT(datetime2(0),convert(VARCHAR,EndDateHejri),103))

And full code is :

SELECT TOP 200   
   SELECT DATEPART(YEAR,CONVERT(datetime2(0),convert(VARCHAR,EndDateHejri),103)) as year
 FROM 
     student

score 1 · Answer 4 · answered May 23 '18 at 12:22

1

Please try below code to get the correct output.

SELECT DATEPART(YEAR,CONVERT(datetime2(0),convert(VARCHAR,EndDateHejri),103));

answered May 23 '18 at 12:22

Vishnu Chandel

113
2
10

How to get year of character hijri date SQL Server

4 Answers4