Use of th, sp and spth in date function

USE OF TH IN TO_CHAR FUNCTION ddth places th, rd, nd for the date (dd) field. SELECT TO_CHAR(SYSDATE, ‘ddth-mon-yy’) FROM dual Output : 6TH-FEB-10 USE OF SP IN TO_CHAR FUNCTION ddsp indicates that the date (dd) must be displayed by spelling the date such as one, fifteen etc SELECT SYSDATE, TO_CHAR(SYSDATE, ‘ddsp’) FROM dual Output […]
Continue reading…

Enjoyed this post? Share it!

 
 

Date functions – new_time

The Oracle/PLSQL function new_time returns date in one timezone to a specified timezone. Syntax: NEW_TIME(DATE, from_timezone, to_timezone) Example code: SELECT SYSDATE "EST time", NEW_TIME(SYSDATE, ‘EST’, ‘PST’) "PST time" FROM dual The output is: EST time : 1/7/2010 6:08:07 AM PST time : 1/7/2010 3:08:07 AM SELECT NEW_TIME(TO_DATE(’01/01/2010 3:08:07 AM’, ‘MM-DD-YYYY HH:MI:SSAM’), ‘EST’, ‘GMT’) FROM dual […]
Continue reading…

Enjoyed this post? Share it!

 
 

Date functions – months_between

The Oracle/PLSQL date function MONTHS_BETWEEN returns the number of months between two dates that are passed as arguments. Syntax: MONTHS_BETWEEN(date1, date2)   Example code:   <pre lang="plsql">SELECT TRUNC(SYSDATE), MONTHS_BETWEEN(SYSDATE, ’31-JUL-2009’) FROM dual trunc(sysdate) months_between ———————————– 12/31/2009 5 SELECT TRUNC(SYSDATE), MONTHS_BETWEEN(SYSDATE, ’11-FEB-2009’) FROM dual trunc(sysdate) months_between ——————————————– 12/31/2009 10.6481179062127 This return fraction of month. trunc can […]
Continue reading…

Enjoyed this post? Share it!

 
 

Date functions – add_months

The Oracle/PLSQL date function ADD_MONTHS adds exactly the specified number of months to the specified date. The number can be positive or negative. Syntax: ADD_MONTHS(DATE, number_of_months_to_add) Example code: SELECT SYSDATE, ADD_MONTHS(SYSDATE, 3) FROM dual sysdate add_months ————————————————- 12/31/2009 2:47:42 AM 3/31/2010 2:47:42 AM SELECT SYSDATE, TRUNC(ADD_MONTHS(SYSDATE, -2)) FROM dual sysdate add_months ——————————— 12/31/2009 10/31/2009
Continue reading…

Enjoyed this post? Share it!

 
 

Date functions – next_day

The Oracle/PLSQL function next_day returns the next date of the specified day of a given date. Syntax: NEXT_DAY(DATE, day_of_week) Example code: SELECT SYSDATE, NEXT_DAY(SYSDATE, ‘MON’) next_monday FROM dual sysdate next_monday ————————————————– 12/31/2009 6:08:23 AM 1/4/2010 6:08:23 AM
Continue reading…

Enjoyed this post? Share it!

 
 

Date functions – last_day

The Oracle/PLSQL function last_day returns the last day of a month for the specified date. Syntax: LAST_DAY(DATE) Example code: SELECT LAST_DAY(SYSDATE) "last day" FROM dual last day ———————- 12/31/2009 11:20:27 PM SELECT LAST_DAY(’07-JUN-09’) "last day" FROM dual last day ———————- 6/30/2009
Continue reading…

Enjoyed this post? Share it!

 
 

Date functions – sysdate

The oracle/PLSQL function sysdate returns the current date/time Example code: SELECT SYSDATE FROM dual SYSDATE ——————– 12/30/2009 10:25:40 PM If only date is needed and not the timestamp, then sysdate can be used with the truncate (trunc) function as shown in the following example. SELECT TRUNC(SYSDATE) FROM dual SYSDATE ——— 12/30/2009 Integers can be used […]
Continue reading…

Enjoyed this post? Share it!