Gregorian date to Hijri date

Posted on 2006-05-27
Last Modified: 2012-05-05
I am Having a table. On which i have one field whose data type datetime also i have the value data on that field, i am having another field whose data type is numeric which contains the arabic date(Hijri calendar date) this is null now.I want to update the arabic date field with the existing English from which i having in another field.
Question by:batchakamal
    LVL 14

    Accepted Solution

    try this,

    update table_name set   hdate = convert(datetime, GETDATE(), 131)
    where <condition>


    Hijri Date
    The SQL Server datetime data type supports only Gregorian dates, but you can use a conversion function to convert the Gregorian date to the Hijri date based on the Kuwaiti algorithm.

    Conversion to and from the Hijri calendar is possible through the CONVERT intrinsic. There are two CONVERT styles that are available in SQL Server 2000 to support Hijri dates:

    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 in its result set:

    7/05/1421 12:14:35:727PM

    The reverse operation is also possible. The following syntax would be used to convert a Hijri date to Gregorian format:

    SELECT CONVERT(datetime, ' 7/05/1421 12:14:35:727PM', 131)

    This query would convert the date to SQL Server datetime type, which in SQL Query Analyzer would appear as follows:

    2000-08-07 12:14:35.727

    Note that SQL Server does not use the regional calendar settings included with Windows 2000, and any adjustment made in that regional setting for the Hijri date does not affect the conversion method of SQL Server.

    Also, SQL Server date functions such as DateName, DatePart, and DateAdd work with the Gregorian calendar, so you must make your conversions programmatically during development.



    LVL 14

    Expert Comment

    by:Thandava Vallepalli
    Both fields must be datetime data type.

    I assume fields names as Field1, Field2.  Field1 has English Date time. Field2 has null values.  Field1 & Field2 are datetime data type.

    I assume table name is <table_name>

    update <table_name> set Field2 = convert( datetime, Field1, 131 )



    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how the fundamental information of how to create a table.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now