Date functions

Date functions can be confusing at times. I used to get confused a lot when i was learning SQL long time ago. Even after 6 years of learning i do sometimes get confused with date functions. Today i am going to write some basic date functions that can be used against oracle database. These simple functions can be very useful in day to day work.

For example purpose i will  be using pseudo table called Dual to write date functions and i will use sysdate as a date field. Now let's get started......


To display first day of month :- There are many ways you could write SQL to display first day of month. However my preferences are as follows:

* select (sysdate -extract(Day from sysdate)+1) AS Date_field from dual;
* select trunc(sysdate,'MM') from dual;

Note :- If you happen to be a teradata developer then you can write following to display first day of month in teradata.

select (date -extract(Day from date)+1) AS Date_field;

To display last day of month :- Displaying last day of month is fairly easy in oracle. The syntax is

select last_day(sysdate) from dual;

To find out when is next Sunday ?

Select next_day(sysdate, 1) from dual;
/ * Or */  select next_day(sysdate,'sunday') from dual; 

Let's  find out how many years are there in 57 months ?

SELECT NUMTOYMINTERVAL(57,'MONTH')
FROM DUAL;

Let's find out how many minutes and seconds are there in 1000 seconds ?

SELECT NUMTODSINTERVAL(1000,'second')
FROM DUAL;

So, at last but not the least, what day is today ? Let's write in words..

 select 'Today is '|| to_char(sysdate,'fmDay ,"the" ddth "of" Month ,Year') Today from dual;

Output :-

TODAY                                                                                
-------------------------------------------------------------------------------------
Today is Monday ,the 7th of March ,Twenty Eleven