=================================================
Website      : http://www.arshltd.com
Author       : Arsh Ltd
Category     : Database Administration
Title        : Date data type handling
Description  : Discusses various ways to handle date data types
=================================================

Background : Following discussion is about date data types and how to retrieve data from tables and format it or use it in your filter conditions. Following are most commonly used formatting.

Queries: In order to demonstrate queries on date data type, assume that you have a table called mytable and a date data type column called dt, using the following –

Create table mytable (
dt   date
);

Insert values in date data type column -

--Following will insert current server date/time 
--(note that sysdate returns current server date/time)
Insert into mytable values (sysdate);

--Following will insert current server date 
--(not time, note that it has been truncated i.e. if we use trunc() 
--with a value which is date/time, it chops off the time element 
--and leaves the date part.
Insert into mytable values (trunc(sysdate));

--Insert a user specified date only 
Insert into mytable values (’01-Jan-2009’);

--Insert a user specified date using to_date() function
Insert into mytable values (to_date(’01-Jan-2009’,’dd-mon-yyyy’));

--Insert date with time using to_date() function
Insert into mytable values (to_date(’01-Jan-2009 10:45:36’,’dd-mon-yyyy hh24:mi:ss’));

Select values from date data type column –

--Select without any formatting (both date and time)
Select dt 
from mytable;

--Select without any formatting (just the date, truncates the time element)
Select trunc(dt) 
from mytable;

--Select with formatting
Select to_char(dt,’dd-mon-yyyy’)
from mytable;

Select to_char(dt,’dd/mm/yyyy’)
from mytable;

Select to_char(dt,’dd.mm.yyyy’)
from mytable;

Select to_char(dt,’dd-mon-yyyy hh24:mi:ss’)
from mytable;

--To find out last day of the month in which “dt” is so e.g. if value in dt is 
--“10-Jan-2009” then the last date of month that will be returned is “31-Jan-2009”
Select last_day(dt) 
from mytable;


--To display first day of the month in which “dt” value is there, so e.g. if dt is 
--“10-Jan-2009” then the first date of month will be “01-Jan-2009”
SELECT TO_DATE('01'||TO_CHAR(dt,'-MON-YYYY'),'DD-MON-YYYY')
FROM mytable;

--To display date on next specific weekday i.e. to display next Friday 
--from column dt, use 
Select next_day(dt,’SAT’) 
From mytable;

--To round date to a particular component or unit e.g. Year, Month etc. i.e. if dt 
--value is “10-Jan-2009” then following select statement will give 2009
Select round(dt,’YEAR’) 
From mytable;

--To display current session timezone
Select SESSIONTIMEZONE
From dual;

--Return date after adding one day to value stored in dt column
Select dt + 1
From mytable;

--Return date/time after adding one hour to value stored in dt column
Select dt + (1/24)
From mytable;

--Return date/time after adding 10 minutes to value stored in dt column
Select dt + (10/(24*60))
From mytable;

--Return date/time after adding 1 calender month to value stored in dt column
Select add_months(dt,1)
From mytable;

--Return date/time after subtracting (reducing) 1 calender month to value 
--stored in dt column
Select add_months(dt,-1)
From mytable;