How to save Hijri dates in sql server tables? What type of column?

Question

How to save Hijri dates (0000/01/01-9999/01/01) in Microsoft SQL Server tables? What type of column?
I choose datetime2, is it right?

CREATE TABLE [dbo].[MyDates](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](50) NULL,
    [Date] [datetime2](0) NULL,
 CONSTRAINT [PK_MyDates] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Answer 1

I dont know why people downvote this but if you have ever written a program that interacts with Persian dates and you need to generate reports based on dates and times you will understand why you should store it as pure string value. sql server lacks the ability to convert UTC to different time zones and you should use clr to do that and many hosting environment does not allow you to do that

I recommend you to store them in fixed format like yyyy/MM/dd HH:mm:ss in char(19) column if you ever want to have reports based on Hijri date, also store the UTC time in another column for further uses or globalization.

If you store them with the pattern I mentioned above you can compare them easily and also get specific part of the date time (year, hour, ...) just by extracting part of the value by using SUBSTRING

Keep in mind that you can always store the UTC time with time zone in your database but my experience shows that you will have difficulties with reports soon or later.

Another point is that never try to store Hijri dates in DateTimes because you can not do that. for example 1394/02/31 is a Hijri date but you can not have 31st day of February in DateTime.

Assume you want to generate report of items that are created in each day. if you store the date just as UTC you must to convert each date time to Hijri by specific time zone difference and daylight time saving and then group by it and you will face huge performance degradation. For example try to calculate the report for 1393/12/20 to 1394/01/10 and then you will understand why not use UTC date times for this kind of reporting stuffs.

Answer 2

Store them in datetime2 -- they should go in as utc dates. If you have strings, convert them using CONVERT passing the style code 131.

DECLARE @HijriDateString VARCHAR(10) = '02/02/1435';
declare @HijriDate datetime2 = CONVERT(DATETIME, @HijriDateString, 131);
declare @FormattedOuput varchar(255) = convert(nvarchar(255), @HijriDate, 131);
select @HijriDateString as [original string], @HijriDate as [parsed datetime2], @FormattedOuput as [formatted output]

-- original string  parsed datetime2              formatted output
-- 02/02/1435       2013-12-05 00:00:00.0000000   2/02/1435 12:00:00.0000000AM

Then when you present the date to the user, you format it back out as Hijri.

By keeping it in datetime2, you can do proper date arithmetic, using things like

MyDateColumn < getutcdate() 

To do correct comparisons.

EDIT for @Dotctor's question Does a normal grouping by day not work? There's every chance I'm missing something key, so I'd be glad to learn

begin tran

create table #hijriDateExample (id int, d datetime2)

insert into #hijriDateExample values 
(1, CONVERT(DATETIME, '02/02/1435 01:00' , 131)),
(2, CONVERT(DATETIME, '02/02/1435 22:00' , 131)),
(3, CONVERT(DATETIME, '03/02/1435 04:00' , 131))

select dateadd(DAY,0, datediff(day,0, d)), count(*) as [numberOfDays] from #hijriDateExample
group by dateadd(DAY,0, datediff(day,0, d))

-- 2013-12-05 00:00:00.000  2
-- 2013-12-06 00:00:00.000  1

rollback tran