Dates are always a confusion when working on most of the languages . . . wait what?
Well the truth is that must of the people never reads a manual because . . . is bigger than the bible.
So here is a simple explanation of the Date Datatype in Oracle Sql. First of all a date data type only stores date + time but only with a precision of seconds if you need to record fractional second then you need to use a different data type.
In almost all the languages the dates can be converted from and to strings directly. well here is not the exception, but you need to be careful because it always depends on the setup of the database.
this 3 parameters:
nls_date_format <---- br="" date="" format="">nls_language <---- br="" database="" language="" of="" the="">nls_territory <--- br="" database="" of="" territory="" the="">You can get the values from them with the following query:
select *
from v$parameter
where name in ('nls_date_format'
,'nls_language'
,'nls_territory');
By default the language and territory are american and america , and the date format is DD-MON-RR, wich means '12-JAN-14' is a valid date that represents 12 January of 2014, that was easy right but . . .
What happens when your source of data has a diferent format, for example
select '07-12-1985' from dual
to make this a date
select to_date('07-12-1985','DD-MM-RRRR') from dua.
If you are bored here begin the tricks
Imagine that somen one ask you to generate a range of dates,
1,2. . . . . 30 jan 2014
how do i this?
Select start_date+level-1 calc_date
from (select to_date('01-01-2014', 'dd-mm-rrrr') start_date,
to_date('31-01-2014', 'dd-mm-rrrr') end_date
from dual) connect by level<=end_date-start_date+1
How to get the first day:
select trunc(to_date('31-01-2014', 'dd-mm-rrrr'),'mm') fisrt_day
from dual;
How to get the last day:
select last_day(to_date('31-01-2014', 'dd-mm-rrrr')) last_day
from dual;
How to add days 12 days:
select to_date('31-01-2014', 'dd-mm-rrrr') + 12 add_days
from dual;
How to add 12 months:
select add_months(to_date('31-01-2014', 'dd-mm-rrrr'),12) add_months
from dual;
How to substract 6 months:
select add_months(to_date('31-01-2014', 'dd-mm-rrrr'),-6 ) add_months
from dual;
**** Yes it's tha same function just using a negative number.
And thats all for now.
Hope it helps some one.
