Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2003
    Posts
    3

    Lightbulb Unanswered: How to Deal with Hijri Dates in SQL Server

    Dear Friends,

    I have one table where the Hijri (Islamic Date) is available in SQL Server vchar data type. Now I have to create report from the table

    The User will provide the FromDate and ToDate as a paramerter.

    The Query works fine, as far as the date are inserted in sequence
    but if they are out, the outcome is not come properly..

    Here is some sample data:

    Table Sample

    Field Name: RegDate
    13/11/1423
    14/11/1423
    15/11/1423
    16/11/1423
    .
    .
    .
    20/11/1423
    28/11/1423
    05/12/1423


    If I query the first three records everthing seems to be okay
    SELECT * From Sample Where RegDate between '13/11/1423' and 16/11/1423

    but if the dates are not in sequence the result in not in expected form..

    Is this becuase I stored Hijri date in varchar data type?

    Any Help will be appricated?

    Regards;

    Imran Jalali.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Yup, your problem is that you are testing an ill formed string for a sequential value. I would suggest using a datetime datatype but the earliest date supported is 01/01/1753. Can you re-order the date components into a YYYYMMDD?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Feb 2003
    Posts
    3

    How to Deal with Hijri Dates in SQL Server?

    Thanks! this is what my reaseach on this conculde that the Georgian Date can't less then 01/01/1753.

    In Other solutions, I found that I will store the Date in Georgian date using the DateTime Data Type, and reterive that data using the CONVERT Function

    CONVERT(varchar, RegDate,131) will get a eqv Hirji Data but again when I send query it didn't give the result, unless it is in order or sequence, If I send a query using out of sequence it didn't work...

    Somebody at office suggested me to insert the date in YYYYMMDD using the numeric data type, then only you can get the proper result.

    Can anybody help me here?

    Cheers,









    Originally posted by Paul Young
    Yup, your problem is that you are testing an ill formed string for a sequential value. I would suggest using a datetime datatype but the earliest date supported is 01/01/1753. Can you re-order the date components into a YYYYMMDD?

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    post your code! The DateTime data type should work for you.

    How will you store a date of '13/11/1423' in a datime datatype?

    Are you converting the date stored in a datetime field to a varchar and then comparing it to your Hirji date string?
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    here is a test I just ran and seems to work okay with the obveous exception that I moved the dates about 4oo years into the future.

    Code:
    if object_ID('dbo.HirjiDateToGregorianDate') is not null drop function dbo.HirjiDateToGregorianDate
    if object_id('tempdb..#Sample') is not null drop table #Sample
    go
    create function HirjiDateToGregorianDate (@Date varchar(35))
    returns datetime
    as
    begin
      RETURN (select cast(substring(@Date,7,4) + substring(@Date,3,4) + substring(@Date,1,2) + substring(@Date,11,20) as datetime))
    end
    go
    create table #Sample(RegDate datetime)
    
    insert into #Sample values(dbo.HirjiDateToGregorianDate('13/11/1823'))
    insert into #Sample values(dbo.HirjiDateToGregorianDate('14/11/1823'))
    insert into #Sample values(dbo.HirjiDateToGregorianDate('15/11/1823'))
    insert into #Sample values(dbo.HirjiDateToGregorianDate('16/11/1823'))
    insert into #Sample values(dbo.HirjiDateToGregorianDate('28/11/1823'))
    insert into #Sample values(dbo.HirjiDateToGregorianDate('20/11/1823'))
    insert into #Sample values(dbo.HirjiDateToGregorianDate('05/12/1823'))
    
    SELECT * From #Sample Where RegDate between dbo.HirjiDateToGregorianDate('13/11/1823') and dbo.HirjiDateToGregorianDate('16/11/1823')
    SELECT * From #Sample Where RegDate between dbo.HirjiDateToGregorianDate('05/11/1823') and dbo.HirjiDateToGregorianDate('28/11/1823')
    
    create index #TmpSample on #Sample(RegDate)
    
    SELECT * From #Sample Where RegDate between dbo.HirjiDateToGregorianDate('13/11/1823') and dbo.HirjiDateToGregorianDate('16/11/1823')
    SELECT * From #Sample Where RegDate between dbo.HirjiDateToGregorianDate('05/11/1823') and dbo.HirjiDateToGregorianDate('28/11/1823')
    
    drop function dbo.HirjiDateToGregorianDate
    drop table #Sample
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Feb 2003
    Posts
    3
    Greetings,

    I didn't put 13/11/1423 it can't be inserted since the datetime have a limit, can't go less then 01/01/1753

    what I have done is I will prompt the user to hijri date in my client application after he enters the hijri date I will conver it into Georgian
    date, and when I create a report, I will do vice-versa e.g from Georgian date to Hijri Again...


    SELECT * from TableName Where convert(varchar,regdate,131)
    between '13/10/1423' and '13/11/1423'

    The From/To Dates will be put by the user of the application.

    Any other ideas to solve this problem?




    Originally posted by Paul Young
    post your code! The DateTime data type should work for you.

    How will you store a date of '13/11/1423' in a datime datatype?

    Are you converting the date stored in a datetime field to a varchar and then comparing it to your Hirji date string?

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    Could you use a User Defined Function to speed the conversion process from Hijri Date to Gregorian? I should take the time to write an example, but it's a bit late and I don't have my notes in from of me...

    Regards,

    Hugh Scott

    Originally posted by Imran Jalali
    Greetings,

    I didn't put 13/11/1423 it can't be inserted since the datetime have a limit, can't go less then 01/01/1753

    what I have done is I will prompt the user to hijri date in my client application after he enters the hijri date I will conver it into Georgian
    date, and when I create a report, I will do vice-versa e.g from Georgian date to Hijri Again...


    SELECT * from TableName Where convert(varchar,regdate,131)
    between '13/10/1423' and '13/11/1423'

    The From/To Dates will be put by the user of the application.

    Any other ideas to solve this problem?

  8. #8
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    -- You are using varchar, so you must use (cannot be indexed)
    select convert(datetime,RegDate,130) between convert(datetime,'13/11/1423',130) and convert(datetime,'16/11/1423',130)
    -- Or add computed column c_RegDate=convert(datetime,RegDate,130) (can be indexed)
    select c_RegDate between convert(datetime,'13/11/1423',130) and convert(datetime,'16/11/1423',130)
    -- Or datatype of column to datetime (can be indexed)
    select RegDate between convert(datetime,'13/11/1423',130) and convert(datetime,'16/11/1423',130)

    Good luck !

  9. #9
    Join Date
    Sep 2003
    Posts
    4

    we have the same problem too!!!!!!!!!!!

    Hello guys:
    we have the same problem too and our projects stop for this stage
    our problem is:we want to insert date into table testDate in Datetime or another dataType in field testDate and extract it in hijry Date format And compare it with two Date.
    acording to the articles we insert it in to the table in nchar dataType and then we want to extract it in datetime dataType using convert function in sql server :

    (( SELECT CONVERT(datetime, ' testDate', 131)
    FROM testDate ))
    that gives this error:

    (( Server: Msg 241, Level 16, State 1, Line 1
    Syntax error converting datetime from character string. ))

    if we want to insert Date in datetime dataType it enter in gregorian Date format.
    while we want Date in hijry format.

  10. #10
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: we have the same problem too!!!!!!!!!!!

    Originally posted by mohsena
    (( SELECT CONVERT(datetime, ' testDate', 131)
    FROM testDate ))
    You are getting the error, because SQL Server can't evaluate the string ' testDate' as a date. You should at least provide a fieldname or a variable containing a string with the format as specified by 131.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  11. #11
    Join Date
    Sep 2003
    Posts
    4
    Hello thank you for your attention
    I should mention that I don't get your answer completely ,tDate is a field that is ncahr datattype
    Sincerely Mohsena

  12. #12
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Okay, so your query should look like

    (( SELECT CONVERT(datetime, tDate, 131)
    FROM testDate ))

    However, 131 isn't a valid conversion style SQL Server 7.0, but it is the correct in SQL Server 2000.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  13. #13
    Join Date
    Sep 2003
    Posts
    4

    about conert fuction

    Thank your for your attention ,I should mention that I use sqlserver 2000
    sincerely Mohsena

  14. #14
    Join Date
    Dec 2011
    Posts
    1

    Cool This is My solution to this Porblem

    Quote Originally Posted by Imran Jalali View Post
    Greetings,

    I didn't put 13/11/1423 it can't be inserted since the datetime have a limit, can't go less then 01/01/1753

    what I have done is I will prompt the user to hijri date in my client application after he enters the hijri date I will conver it into Georgian
    date, and when I create a report, I will do vice-versa e.g from Georgian date to Hijri Again...


    SELECT * from TableName Where convert(varchar,regdate,131)
    between '13/10/1423' and '13/11/1423'

    The From/To Dates will be put by the user of the application.

    Any other ideas to solve this problem?

    Hi man, Hi All...

    My English isn't good, But I'll try to explain my solution in good manner.

    first I made Customer table that contain hijri date, with column name= ReservationDate and type= datetime ..
    second I made dateTimeFilter table with two columns id, Date[type=datetime]. this table contain two rows, used to store criteria fields
    id / date
    1 / 02/02/1432 12:00:00 AM
    2 / 13/01/1433 10:24:00 PM
    ----------------------------------------------

    now I'm writing select statement in this manner without problems,

    SELECT * FROM Customer
    WHERE (ReservationDate BETWEEN
    (SELECT date FROM dateTimeFilter WHERE (id = 1))
    AND
    (SELECT date FROM dateTimeFilter WHERE (id = 2)))




    ----------------------------
    Good luck ..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •