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

Answer 1

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