OraFAQ Forum: Reports & Discoverer

Home » Developer & Programmer » Reports & Discoverer » Hijri Dates (Windows XP DB 9i - forms 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Hijri Dates [message #395170]

Tue, 31 March 2009 09:43

*munnabhai*
Messages: 157
Registered: March 2008
Location: Riyadh

Senior Member

Hi Guyz,

i have one problem with my report, can anyone guide me how to solve the below problem.

i have a date in hijri and it is displaying well in forms as well in sql like 1420/02/13 but when i create report and run it change the format like 13-feb-2005 in this format how can i convert date in hijri in reports.

This below function converts gregorian date to hijiri date

select to_char(to_date('20090111','YYYYMMDD'),'YYYY/MM/DD', 'NLS_CALENDAR='''||ARABIC HIJRAH''')FROM DUAL;

how can i use the above funtion in reports?

anyhelp will be appreciate.

Regards

Report message to a moderator

Re: Hijri Dates [message #397683 is a reply to message #395170]

Mon, 13 April 2009 06:42

AshrafKittaneh26
Messages: 18
Registered: June 2008
Location: UAE

Junior Member

Hi Guys

regarding this topic, i have a form in a non oracle application.
and i want to save the hijri (arabic date) in a text_item of a type of date. becuase i want to use this feild in search later on so i have to keep the type of the text_item to be date.

now the problem i had faced that when i enter the date 31/02/2009 for example it gives me error:

FRM-50004: day must be between 1 and last of month.
any idea is Appreciated.

One more question:
that how to use a hijri calendar. and if i could i want to select unlimited date "more than 100 years before" Embarassed

thanks in advance.
Smile

Report message to a moderator

Re: Hijri Dates [message #397694 is a reply to message #395170]

Mon, 13 April 2009 07:06

*munnabhai*
Messages: 157
Registered: March 2008
Location: Riyadh

Senior Member

Hi Ashraf,

In your first query there is date format problem.

use the below code in your sql command and do your query
hope this will solve your first problem

alter session set nls_calendar='Gregorian';
alter session set nls_date_format='DD/MM/YYYY';

alter session set nls_calendar='Gregorian';
alter session set nls_date_format='YYYY/MM/DD';

alter session set nls_calendar='Arabic Hijrah';
alter session set nls_date_format='YYYY/MM/DD';

for your second query using hijri calendar below is the code.

-- This function converts  hijiri date to gregorian date
select to_date('14290509','YYYYMMDD', 'NLS_CALENDAR = '''||'ARABIC HIJRAH''') frin dual;

-- This function converts  gregorian date to hijiri date  
select to_char(to_date('20090111','YYYYMMDD'),'YYYYMMDD', 'NLS_CALENDAR = '''||'ARABIC HIJRAH''') from dual;

it is more simple as we think. you can use any calendar in your forms, im using hijri-to-gregorian and gregorian-to-hijri in my forms i'm attaching snap of my form for your understanding.

/forum/fa/6020/0/

if you need more details give me your email id i will forward a copy of my form to you.

Regards

Moazam

Attachment: HIJRI.JPG
(Size: 13.75KB, Downloaded 3157 times)

Report message to a moderator

Re: Hijri Dates [message #397701 is a reply to message #395170]

Mon, 13 April 2009 07:39

AshrafKittaneh26
Messages: 18
Registered: June 2008
Location: UAE

Junior Member

thank you man,
thats nice of you that you reply to my question.
but you know my big problem is how to force the user to insert the hijri date in a correct format "you know that the date format in form dosnt support hijri date like 31/02/1429"

so what is the correct format for the date text_item
or do you have an ideas to save this date in a column of date data type.

and this is my Email:

ashraf_ketaneh@hotmail.com
thanks in advance Smile

Report message to a moderator

Re: Hijri Dates [message #397707 is a reply to message #395170]

Mon, 13 April 2009 08:13

*munnabhai*
Messages: 157
Registered: March 2008
Location: Riyadh

Senior Member

there are many ways to force user to insert data in the particular field using validation triggers like WHEN-VALIDATE-RECORD.

or you can use format mask (YYYY/DD/MM) in property of text_item

the correct format for hijri date is "YYYY/MM/DD" (1429/02/31).

Regards

Moazam

Report message to a moderator

Re: Hijri Dates [message #397708 is a reply to message #395170]

Mon, 13 April 2009 08:25

AshrafKittaneh26
Messages: 18
Registered: June 2008
Location: UAE

Junior Member

thats right; this format correct but for Gregorian date.
but when assign this format mask "YYYY/DD/MM" to the text item
and i entered this date "1430/31/02" which the user should enter
it gives me this error:

FRM-50004 Day must be between 1 and last of month
as in the attached file.
thanks.

Attachment: untitled.JPG
(Size: 6.88KB, Downloaded 1128 times)

Report message to a moderator

Re: Hijri Dates [message #397713 is a reply to message #395170]

Mon, 13 April 2009 08:35

*munnabhai*
Messages: 157
Registered: March 2008
Location: Riyadh

Senior Member

I gave you three nls code in my first message run in your sql command it will effect.
below is the code

alter session set nls_calendar='Gregorian';
alter session set nls_date_format='YYYY/MM/DD';

run the above code first the run the form hope this will solve your problem.

Regards

Report message to a moderator

Re: Hijri Dates [message #397793 is a reply to message #395170]

Mon, 13 April 2009 23:29

AshrafKittaneh26
Messages: 18
Registered: June 2008
Location: UAE

Junior Member

thanks man i'll try it and let you know.
Cool

Report message to a moderator

Re: Hijri Dates [message #397905 is a reply to message #395170]

Tue, 14 April 2009 08:22

AshrafKittaneh26
Messages: 18
Registered: June 2008
Location: UAE

Junior Member

Thanks man,

It's work perfectly Wink

thanks in advance.

Report message to a moderator

Previous Topic:	Date format error for 10g Reports NLS_DATE_FORMAT
Next Topic:	Report 2.5 inserting results of report into a table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 12 05:55:39 CST 2025