How to get year of character hijri date SQL Server

pbgvytdp  于 2023-10-15  发布在  SQL Server
关注(0)|答案(5)|浏览(144)

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

yshpjwxd

yshpjwxd1#

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

c0vxltue

c0vxltue2#

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;
z9smfwbn

z9smfwbn3#

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
nfg76nw0

nfg76nw04#

Please try below code to get the correct output.

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

8fq7wneg5#

SELECT TOP 200
CAST(FORMAT(EndDateHejri,'yyyy','ar') as int) [EndDateHejriYear] FROM student

相关问题