Home » Developer & Programmer » Reports & Discoverer » Hijri Dates (Windows XP DB 9i - forms 6i)
Hijri Dates [message #395170] Tue, 31 March 2009 09:43 Go to next message
*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
Re: Hijri Dates [message #397683 is a reply to message #395170] Mon, 13 April 2009 06:42 Go to previous messageGo to next message
AshrafKittaneh26
Messages: 18
Registered: June 2008
Location: UAE
Junior Member
Hi Guys Smile

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
Re: Hijri Dates [message #397694 is a reply to message #395170] Mon, 13 April 2009 07:06 Go to previous messageGo to next message
*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.

./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 1036 times)
Re: Hijri Dates [message #397701 is a reply to message #395170] Mon, 13 April 2009 07:39 Go to previous messageGo to next message
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
Re: Hijri Dates [message #397707 is a reply to message #395170] Mon, 13 April 2009 08:13 Go to previous messageGo to next message
*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
Re: Hijri Dates [message #397708 is a reply to message #395170] Mon, 13 April 2009 08:25 Go to previous messageGo to next message
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 229 times)
Re: Hijri Dates [message #397713 is a reply to message #395170] Mon, 13 April 2009 08:35 Go to previous messageGo to next message
*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

Re: Hijri Dates [message #397793 is a reply to message #395170] Mon, 13 April 2009 23:29 Go to previous messageGo to next message
AshrafKittaneh26
Messages: 18
Registered: June 2008
Location: UAE
Junior Member
thanks man i'll try it and let you know.
Cool
Re: Hijri Dates [message #397905 is a reply to message #395170] Tue, 14 April 2009 08:22 Go to previous message
AshrafKittaneh26
Messages: 18
Registered: June 2008
Location: UAE
Junior Member
Thanks man,

It's work perfectly Wink
thanks in advance.
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:
  


Current Time: Wed Dec 07 03:23:50 CST 2016

Total time taken to generate the page: 0.44734 seconds