| Hijri Calendar Problem (2 Merged) [message #493155] |
Sat, 05 February 2011 15:25  |
 |
alimesleh
Messages: 2
Registered: February 2011
Location: Jordan
|
Junior Member |
|
|
Dear All,
i face the following problem ,
in my company they use hijri date
so i set the database nls_calendar = english hijrah on database server and on client's pc's
then i face a deference between the real hijri date with oracle hijri date
so i searched the web and i found a utility called lxegen
to add or subtract a day or more from oracle calendar
the main problem that i face now
assume that the real hijri date on 03/02/2011 was 29/02/1432 in hijri
but in oracle was 01/03/1432 so i used the lxegen to subtract 2 days
now when i select the date from sql plus in database folder its give me 29/02/1432 but if i used the sql plus from form's folder its return 01/03/1432 this problem also in forms and reports
is there anyway to fix this problem
by the way i am using oracle database 10gr2 with oracle developer 6i
please help ASAP
|
|
|
|
|
|
|
|
| Re: Hijri Calendar Problem (2 Merged) [message #493401 is a reply to message #493210] |
Mon, 07 February 2011 14:45  |
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
|
Senior Member |
|
|
there is date format problem.
EXAMPLE
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.
and these r the parameters
Table B-1 NLS_LANG Parameter Values
Operating System Locale NLS_LANG Value
Arabic (U.A.E.) ARABIC_UNITED ARAB EMIRATES.AR8MSWIN1256
Bulgarian BULGARIAN_BULGARIA.CL8MSWIN1251
Catalan CATALAN_CATALONIA.WE8MSWIN1252
Chinese (PRC) SIMPLIFIED CHINESE_CHINA.ZHS16GBK
Chinese (Taiwan) TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950
Croatian CROATIAN_CROATIA.EE8MSWIN1250
Czech CZECH_CZECH REPUBLIC.EE8MSWIN1250
Danish DANISH_DENMARK.WE8MSWIN1252
Dutch (Netherlands) DUTCH_THE NETHERLANDS.WE8MSWIN1252
English (United Kingdom) ENGLISH_UNITED KINGDOM.WE8MSWIN1252
English (United States) AMERICAN_AMERICA.WE8MSWIN1252
Estonian ESTONIAN_ESTONIA.BLT8MSWIN1257
Finnish FINNISH_FINLAND.WE8MSWIN1252
French (Canada) CANADIAN FRENCH_CANADA.WE8MSWIN1252
French (France) FRENCH_FRANCE.WE8MSWIN1252
German (Germany) GERMAN_GERMANY.WE8MSWIN1252
Greek GREEK_GREECE.EL8MSWIN1253
Hebrew HEBREW_ISRAEL.IW8MSWIN1255
Hungarian HUNGARIAN_HUNGARY.EE8MSWIN1250
Icelandic ICELANDIC_ICELAND.WE8MSWIN1252
Indonesian INDONESIAN_INDONESIA.WE8MSWIN1252
Italian (Italy) ITALIAN_ITALY.WE8MSWIN1252
Japanese JAPANESE_JAPAN.JA16SJIS
Korean KOREAN_KOREA.KO16MSWIN949
Latvian LATVIAN_LATVIA.BLT8MSWIN1257
Lithuanian LITHUANIAN_LITHUANIA.BLT8MSWIN1257
Norwegian NORWEGIAN_NORWAY.WE8MSWIN1252
Polish POLISH_POLAND.EE8MSWIN1250
Portuguese (Brazil) BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252
Portuguese (Portugal) PORTUGUESE_PORTUGAL.WE8MSWIN1252
Romanian ROMANIAN_ROMANIA.EE8MSWIN1250
Russian RUSSIAN_CIS.CL8MSWIN1251
Slovak SLOVAK_SLOVAKIA.EE8MSWIN1250
Spanish (Spain) SPANISH_SPAIN.WE8MSWIN1252
Swedish SWEDISH_SWEDEN.WE8MSWIN1252
Thai THAI_THAILAND.TH8TISASCII
Spanish (Mexico) MEXICAN SPANISH_MEXICO.WE8MSWIN1252
Spanish (Venezuela) LATIN AMERICAN SPANISH_VENEZUELA.WE8MSWIN1252
Turkish TURKISH_TURKEY.TR8MSWIN1254
Ukrainian UKRAINIAN_UKRAINE.CL8MSWIN1251
Vietnamese VIETNAMESE_VIETNAM.VN8MSWIN1258
i hope u got everything
|
|
|
|
| Re: Hijri Calendar Problem (2 Merged) [message #493403 is a reply to message #493401] |
Mon, 07 February 2011 14:50 |
 |
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
I think you misunderstood OP's problem which is not to get Oracle Hijrah date but "i face a deference between the real hijri date with oracle hijri date".
This is clearly stated by his example:
Quote:assume that the real hijri date on 03/02/2011 was 29/02/1432 in hijri but in oracle was 01/03/1432
Regards
Michel
[Updated on: Mon, 07 February 2011 14:51] Report message to a moderator |
|
|
|
| Re: Hijri Calendar Problem (2 Merged) [message #493406 is a reply to message #493403] |
Mon, 07 February 2011 15:04 |
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
|
Senior Member |
|
|
i think this was the example need to use skills
one more example
select
to_char(sysdate,'day dd month yyyy','nls_calendar=''Arabic Hijrah'''),
to_char(sysdate,'day dd month yyyy','nls_calendar=''English Hijrah'''),
to_char(sysdate,'day dd month yyyy','nls_calendar=''gregorian''')
from dual ;
to make things a bit more clear:
alter session set nls_calendar = 'Arabic Hijrah';
gives the names of the Hijrah months in Arabic
so you client needs to be configured to display Arabic. -> Note 179133.1
alter session set nls_calendar = 'English Hijrah';
gives the names of the Hijrah months in english.
alter session set nls_calendar = 'gregorian';
gives the names of the Gregorian months (jan-feb-etc).
could be misunderstood i accepted pls you can clear for forum users thnaks we shall be epreciate u
thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
