working on Hijri dates... facing error... plzzz help

Question

0.00/5 (No votes)

See more:

i have a table with G-date n Hijri-date,
where i have these col as varchar(10) datatype in d table.

my stored procedure:

USE [RAS]
GO
/****** Object:  StoredProcedure [dbo].[Sp_InsertEmployee]    Script Date: 02/12/2014 17:26:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[Sp_InsertEmployee]
(			
			@Branchcode int
           ,@Joinningdate date
           ,@Empname nvarchar(50)      
           ,@IqamaExp varchar(10)
           ,@IqamaExpArabic varchar(10)
)

-- Sp_InsertEmployee 1,null,null,1,1,1,1,null,'02/02/2014','02/02/1435',null,null,null,null,null,null,null,null,null,null,null,null,null,null,1

AS BEGIN
INSERT INTO [RAS].[dbo].[tbl_Employee]
           ([Branchcode]
           ,[Joinningdate]
           ,[Position]
           ,[IqamaExp]
           ,[IqamaExpArabic]
          )
     VALUES
     (			
			@Branchcode 
           ,@Joinningdate 
           ,@Empname 
          ,convert(varchar(10),@IqamaExp, 101)
           ,convert(varchar(10),@IqamaExpArabic, 101)
           
     )
           
END

print CONVERT(datetime,@IqamaExp, 101)
print CONVERT(datetime,@IqamaExpArabic, 101)

result:

(1 row(s) affected)
Feb  2 2014 12:00AM
Msg 242, Level 16, State 3, Procedure Sp_InsertEmployee, Line 91
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

when i'm insert it in d storedprocedure, its showing me this error..

but, if i call this from asp.net page... its showing converting date / time error...

plz ... help me

Posted 12-Feb-14 6:27am

abdul subhan mohammed

Add a Solution

Comments

Andrius Leonavicius 12-Feb-14 12:57pm

Hi,

Why you're not storing your dates as DATETIME in SQL? You can format a DATETIME value into a specific date format in ASP.NET...

Sergey Alexandrovich Kryukov 12-Feb-14 16:56pm

I doubt that the standard DATETIME or DATE data types are compatible with Hijri. Did you pay attention that OP is using not a "regular" Western calendar, but Hijri?
—SA

abdul subhan mohammed 12-Feb-14 13:08pm

Actually i'm getting error in convert hijri date,
like: 02/02/1435... so sql is not accepting 1435 yr, its showing out of range...

Sergey Alexandrovich Kryukov 12-Feb-14 16:58pm

You are just confirming the fact that standard DATETIME is incompatible with the date OP needs to represent.
I tried to provide an advice on what to do, please see Solution 1.
—SA

Andrius Leonavicius 12-Feb-14 18:27pm

Thanks for noticing. I made an adjustment.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andrius Leonavicius · Answer 1 · 2014-02-12T12:24:00

Here is another possible solution. You can store your dates in DATETIME data type (Gregorian calendar) and use convert functions between Gregorian and Hijri dates.

EXAMPLES:

Hijri date to Gregorian date:

SQL

DECLARE @HijriDate VARCHAR(10) = '02/02/1435';
SELECT CONVERT(DATETIME, @HijriDate, 131);

Gregorian date: 2013-12-05 00:00:00.000

Gregorian date to Hijri date:

SQL

DECLARE @GregorianDate DATETIME = '02/13/2014';
SELECT CONVERT(VARCHAR(25), @GregorianDate, 131);

Hijri date: 13/04/1435 12:00:00:000AM

You can find more information here:
1. SQL Server Hijri (Hijra) Dates[^]
2. SQL SERVER – How to convert Gregorian dates to Hijri date with Formatting[^]

---------------------------------------------------------------------------------------
THIS IS IMPORTANT!!!

I did some research and found out this:
Unfortunately, this solution works correctly only for the Kuwaiti Hijri calendar.

I'm going to illustrate the problem. According to Umm al-Qura calendar 30/02/1434 is a valid date (matches 01/12/2013). But if you try this:

SQL

DECLARE @HijriDate VARCHAR(10) = '30/02/1434';
SELECT CONVERT(DATETIME, @HijriDate, 131);

you are going to get this:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

This happened because according to the Kuwaiti algorithm 30/02/1434 is not a valid date. "Hijri is a calendar system with several variations. SQL Server uses the Kuwaiti algorithm." - Microsoft. In comparison, Google Calendar currently supports three major variations of Hijri calendar.

Other possible solutions:
1. Look at the solution 1.
2. Store your dates as DATETIME in SQL Server (Gregorian) and implement date conversion in ASP.NET (Hijra, Umm al-Qura). This might be useful: Um Al Qura (Hijri) Calendar[^]

Sergey Alexandrovich Kryukov · Answer 2 · 2014-02-12T11:04:00

Please see the discussions on the comment to the question. It looks like standard DATE and DATETIME data type are designed for (limited!) support of "regular" Western (Gregorian) calendar and are not suitable for Hijri, which us actually more complex, due to its historical roots: http://en.wikipedia.org/wiki/Hijri[^].

My initial idea was: you would need to develop your own data type to represent Hijri dates. It can possibly use more than one integer column in the date table. It is important not to try to represent the Hijri as a string; always use some numeric types to represent the components of the point in time. Follow the definition of the calendar you can build from the page referenced above or other reliable sources. Also, you can take a look at how other people solved this problem: http://bit.ly/1bpEPQW[^].

—SA

working on Hijri dates... facing error... plzzz help

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us