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 : 6/2/2010, six
USE OF SPTH IN TO_CHAR FUNCTION
spth displays the date(dd) with ‘th’ added to the spelling of date.
SELECT TO_CHAR(SYSDATE, 'ddspth') FROM dual
OUTPUT : SIXTH
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
The output is in GMT : 1/1/2010 8:08:07 AM
Get first day of month using PLSQL
Following PLSQL code can be used get first day of any month.
SELECT TRUNC(TO_DATE('07-JUL-2009'), 'MM') FROM dual
The output is : 7/1/2009
SELECT TRUNC(SYSDATE), TRUNC(TO_DATE(SYSDATE),'MM') first_day FROM dual
sysdate first_day
1/6/2010 1/1/2010
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 be used to get the whole number as shown in the following code
SELECT trunk(SYSDATE), TRUNC(MONTHS_BETWEEN(SYSDATE, '11-FEB-2009')) FROM dual
trunc(sysdate) months_between
——————————————-
12/31/2009 10
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
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
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
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 to add or subtract number of days from the current date as in the following example. trunc can also be used with + and –
SELECT SYSDATE + 1 FROM dual
SYSDATE
——————–
12/31/2009 10:25:40 PM
SELECT TRUNC(SYSDATE) - 3 FROM dual
SYSDATE
———
12/27/2009
