تبدیل تاریخ ها در اوراکل

تاريخ شمسي در اوراکل

Select To_char(sysdate,'YYYY/MM/DD','NLS_CALENDAR=''Persian''')
from dual
Result => 1394/05/10

تاريخ ميلادي

Select To_char(sysdate,'YYYY/MM/DD','NLS_CALENDAR=''Gregorian''')
from dual
Result => 2015/08/01

تاريخ قمري

Select To_char(sysdate,'YYYY/MM/DD','NLS_CALENDAR=''Arabic hijrah''') from dual
Result => 1436/10/15

تبديل تاريخ ميلادي به شمسي

Select To_char(To_date('2015/08/01','YYYY/MM/DD','NLS_CALENDAR=''Gregorian'''),'yyyy/mm/dd') from dual
Result => 1394/05/10

تبدیل تاریخ شمسی به میلادی در اوراکل

select
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD'),'YYYY/MM/DD','NLS_CALENDAR=persian')
,'YYYY/MM/DD','NLS_CALENDAR=GREGORIAN') as md
from dual
Result => 2019/10/30

اضافه كردن ماه به تاريخ

Select add_months(date'2015-02-20',1) from dual
Result => 3/21/2015

تبديل رشته به تاريخ

select date '1394-02-02' from dual
Result => 2/2/1394

تبديل به Timestamp

select timestamp '2015-02-20 12:00:00'
from dual
Result => 12/1/1393 12:00:00.000000000

تبديل Timestamp به رشته

select to_char(timestamp '2015-02-20 12:00:00','YYYY/MM/DD HH24:MI')
from dual
Result => 1393/12/01 12:00

محاسبه اختلاف دقيقه اي بين دو تاريخ

select round((to_date('1394/04/05 12:30','yyyy/mm/dd hh24:mi')
-to_date('1394/04/04 11:30','yyyy/mm/dd hh24:mi')) * 24 * 60,0) as result_in_minutes
from dual
Result => 1500

حذف ساعت از تاریخ در اوراکل

select trunc(sysdate) from dual
Result => 11/2/2019

تنظيمات تاريخ برای استفاده در JOB های اوراکل

Every day at midnight=TRUNC(SYSDATE+1)
Every 7 days at midnight=TRUNC(SYSDATE+7)
Every 30 days at midnight=TRUNC(SYSDATE+30)
Every Sunday at midnight=NEXT_DAY(TRUNC(SYSDATE), 'SUNDAY')
Every day at 6am=TRUNC(SYSDATE+1)+6/24
Every 30 minutes=SYSDATE+30/1440
Every 1st of the month at midnight=TRUNC(LAST_DAY(SYSDATE)) + 1
Every 1st of the month at 8:30 am=TRUNC(LAST_DAY(SYSDATE)) + 1 + 8/24 + 30/1440