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 , 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 don’t know why people underestimate this, but if you have ever written a program that interacts with Persian dates and you need to create reports based on dates and times, you will understand why you should store it as a pure string value. sql server is not able to convert UTC to different time zones, and you should use clr for this, and many hosting environments do not allow you to do this

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

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

Remember that you can always store UTC time with a time zone in your database, but my experience shows that you will soon encounter messages.

Another thing is that never try to store Hijri dates in DateTimes, because you cannot do this. for example 1394/02/31 is a hijri date, but you cannot have the 31st day of February in DateTime.

Suppose you want to report on items created every day. if you store the date exactly like UTC, you must convert each time to Hijri using a specific time zone difference and saving daylight saving time, and then grouping it together, and you will face a huge performance degradation. For example, try to calculate a report for 1393/12/20 to 1394/01/10 , and then you will understand why not use UTC date time for this type of reporting material.

Answer 2

Store them in datetime2 - they should be entered 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 give the date to the user, you will format it as Hijri.

By storing this in datetime2, you can do the correct date arithmetic using things like

 MyDateColumn < getutcdate() 

Make the right comparisons.

EDIT for @Dotctor question. Does regular grouping work in the afternoon? There is every chance that I am missing something key, so I would be glad to know.

 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 

Answer 3

You can use a column like (Hijri_Date varchar (10)), and it will be easy to save and easily create reports and queries between two dates. C # asp.net uses the following steps:

1- Add a script manager to your web form. and set the property EnableScriptGlobalization = true, EnableScriptLocalization = true

2- Add a HijriDate text box.

3- Add button to select Hijra date.

4- Add CalendarExtender from Ajaxtoolbox and add the following properties, for example PopupButtonID = "btnDate" TargetControlID = "txtapptdate".

5- Change the format property for CalendarExtender to dd / MM / yyyy.

6- change the property for a read-only text field = true.

7- Add the following culture values ​​to your home page in html to convert the calendar from a Gregorian date to a hijri date: UICulture = "ar" Culture = "ar-SA".

8- When you save and paste the date into the SQL server, you can use the value as usual Hijri_Date.Text

9-, to compare later and create a filter and search screen or reports, and if you need to find entries between two dates, use the format function to change the DATE output format, and the convert function to convert varchar to date in the select statement, for example like this :

 select format(cast(Hijri_date as date),'dd/MM/yyyy','ar-sa') from Table1 where Hijri_date between '10/05/1439' and '20/10/1439' 

Answer 4

It helps me a lot.

 Declare @Hdate nvarchar(50) set @Hdate = convert(varchar(10),convert(date,convert(varchar(12),getdate(),131),103),112)