none
Hijri Date Time store in SQL server DB RRS feed

  • Question

  • Hello

    I have a problem and I searched a lot without any benefits

    How can I store hijri date in datetime field in SQL server 2005

    Is SQL server db support this?

    And how can I deal with this problem and generate crystal report with hijri dates.

    Thanx,,

    : )

     

    Wednesday, April 28, 2010 5:39 PM

Answers

  • Don't know if this is helpful because you're using SQL Server 2005, but SQL Server 2008 has expanded datetime ranges with the datetime2 data type. SQL Server CONVERT function can convert to Hijri datetime style (style 130, 131).

    There is also a SQLCLR sample that encapsulates a calendar-aware datetime. http://msftengprodsamples.codeplex.com/wikipage?title=Calendar-Aware%20Date%2fTime%20UDTs

    Hope this helps, Bob Beauchemin, SQLskills

     

    • Marked as answer by KJian_ Thursday, May 6, 2010 2:12 AM
    Wednesday, April 28, 2010 9:19 PM
    Moderator
  •  

    I agree with Bob, the SQL server 2005 doeasn't support storing datetime as Hijridate, You can use a conversion function to convert the Gregorian date to the Hijri date based on the Kuwaiti algorithm as below:

    130: Returns the date using the Hijri calendar, in dd mon yyyy hh:mi:ss:mmmAM format.

    131: Returns the date using the Hijri calendar, in dd/mm/yy hh:mi:ss:mmmAM format.

    For example, to convert a Gregorian date to Hijri format with Transact-SQL, you would use syntax such as the following:

    SELECT

     

    CONVERT(nchar, GETDATE(), 131)

    This query will return a string such as the following:

    16/05/1431 1:36:43:293AM

    To reverse the operation , The following syntax would be used to convert a Hijri date to Gregorian format:

    SELECT

     

    CONVERT(datetime, '16/05/1431 1:36:43:293AM', 131)

    This query would convert the date to SQL Server datetime type as follows:

    2010-04-29 01:36:43.293

    Regards,

    Tarek Ghazali

    SQL Server MVP

     

    • Marked as answer by KJian_ Thursday, May 6, 2010 2:12 AM
    Wednesday, April 28, 2010 9:41 PM

All replies

  • Don't know if this is helpful because you're using SQL Server 2005, but SQL Server 2008 has expanded datetime ranges with the datetime2 data type. SQL Server CONVERT function can convert to Hijri datetime style (style 130, 131).

    There is also a SQLCLR sample that encapsulates a calendar-aware datetime. http://msftengprodsamples.codeplex.com/wikipage?title=Calendar-Aware%20Date%2fTime%20UDTs

    Hope this helps, Bob Beauchemin, SQLskills

     

    • Marked as answer by KJian_ Thursday, May 6, 2010 2:12 AM
    Wednesday, April 28, 2010 9:19 PM
    Moderator
  •  

    I agree with Bob, the SQL server 2005 doeasn't support storing datetime as Hijridate, You can use a conversion function to convert the Gregorian date to the Hijri date based on the Kuwaiti algorithm as below:

    130: Returns the date using the Hijri calendar, in dd mon yyyy hh:mi:ss:mmmAM format.

    131: Returns the date using the Hijri calendar, in dd/mm/yy hh:mi:ss:mmmAM format.

    For example, to convert a Gregorian date to Hijri format with Transact-SQL, you would use syntax such as the following:

    SELECT

     

    CONVERT(nchar, GETDATE(), 131)

    This query will return a string such as the following:

    16/05/1431 1:36:43:293AM

    To reverse the operation , The following syntax would be used to convert a Hijri date to Gregorian format:

    SELECT

     

    CONVERT(datetime, '16/05/1431 1:36:43:293AM', 131)

    This query would convert the date to SQL Server datetime type as follows:

    2010-04-29 01:36:43.293

    Regards,

    Tarek Ghazali

    SQL Server MVP

     

    • Marked as answer by KJian_ Thursday, May 6, 2010 2:12 AM
    Wednesday, April 28, 2010 9:41 PM
  • Please I need help about  Hijri Date  I am useing PowerBulder 12  and Sql Server 2008 R2  Please how i can save arabic or Hijri Date  pelase soooooon 

     

    albloshi@yahoo.com

     

    Friday, November 26, 2010 10:16 PM
  • Hi Hussain,

    Refer to Tarek's answer above. If there's a specific problem with PowerBuilder, you might get a more PowerBuilder-specific answer on the vendor's forum.

    SQL Server's datetime series just stores a binary value; you can use the convert function to store/return dates from whatever format you'd like. If you're having problem with dates before (Gregorian-year) 1753, you can use SQL Server 2008's new DATE or DATETIME2 data types, both of which have the ANSI standard value range.

    Hope this helps, Cheers, Bob Beauchemin, SQLskills

    Saturday, November 27, 2010 5:31 PM
    Moderator
  •  

    I agree with Bob, the SQL server 2005 doeasn't support storing datetime as Hijridate, You can use a conversion function to convert the Gregorian date to the Hijri date based on the Kuwaiti algorithm as below:

    130: Returns the date using the Hijri calendar, in dd mon yyyy hh:mi:ss:mmmAM format.

    131: Returns the date using the Hijri calendar, in dd/mm/yy hh:mi:ss:mmmAM format.

    For example, to convert a Gregorian date to Hijri format with Transact-SQL, you would use syntax such as the following:

    SELECT

     

    CONVERT(nchar, GETDATE(), 131)

    This query will return a string such as the following:

    16/05/1431 1:36:43:293AM

    To reverse the operation , The following syntax would be used to convert a Hijri date to Gregorian format:

     

    SELECT

     

     CONVERT(datetime, '16/05/1431 1:36:43:293AM', 131)

     

    This query would convert the date to SQL Server datetime type as follows:

    2010-04-29 01:36:43.293

    Regards,

    Tarek Ghazali

    SQL Server MVP

     

    Hi Mr. Tarek, Hi All,
    can you help me, 
    I want to use hijri date in filtering when retrieving data from database, like this:

    select * from students where regDate between '12-12-1433' and '16-12-1433'

     

    Please Help me ..... ....   

    Friday, December 9, 2011 12:27 PM
  • I have string column with Hijri dates in format MM/dd/yyyy and the 

    CONVERT(datetime, '16/05/1431 1:36:43:293AM', 131)

    needs format dd/MM/yyyy.

    Do you have an idea what can I do?

    Thanks In advance

    Sunday, February 2, 2014 9:16 PM
  • It would probably be best to write a string parsing/conversion function to convert between mm/dd/yyyy.... and dd/mm/yyyy.... formats, using either T-SQL string functions or SQLCLR.

    Cheers, Bob

    Monday, February 3, 2014 8:03 AM
    Moderator
  • try this 

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


    kim El Song

    Monday, June 10, 2019 4:02 AM