Here is a list of the Oracle Date functions. I had enough problems with the Oracle dates in the past, so I created this list, maybe someone will find it useful.
Oracle SYSDATE function
Returns the current date and time set for the operating system on which the database resides.
| Syntax: | SYSDATE |
| Example: | SELECT SYSDATE FROM dual; |
Oracle CURRENT_DATE function
Returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE.
| Syntax: | CURRENT_DATE |
| Example: | SELECT CURRENT_DATE FROM dual; |
Oracle "+" and "-" (plus/minus) date operators
Add or substract a number of days from a date.
| Syntax: | {date} + {integer}
{date} - {integer} |
| Example: | SELECT SYSDATE + 1 FROM dual; SELECT SYSDATE - 1 FROM dual; |
Oracle ADD_MONTHS function
Function to add a number of months to a date. This could be rounded to below if the resulting month has fewer days than the month this function is applied to.
| Syntax: | ADD_MONTHS |
| Example: | SELECT add_months(SYSDATE, 2) FROM dual; |
Oracle DUMP function
Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of a value.
| Syntax: | DUMP({value} [,{return_format}[,{start_position}[,{length}]]]){value}{return_format}{start_position}{length}{value}{return_format}{start_position}{length} |
| Example: | SELECT add_months(SYSDATE, 2) FROM dual; |
Oracle GREATEST function
With the greatest function you can select the date/time that is the highest in a range of date/times.
| Syntax: | GREATEST |
| Example: | SELECT GREATEST(SYSDATE-7, SYSDATE-9, SYSDATE+2)FROM t; |
Oracle INTERVAL function
Interval to adjust date-time.
| Syntax: | INTERVAL '{integer}' {unit} |
| Example: | SELECT TO_CHAR(SYSDATE + INTERVAL '10' MINUTE, 'HH:MI:SS') FROM dual; |
Oracle LEAST function
With the least function you can select the earliest date/time in a range of date/times.
| Syntax: | LEAST({date}, {date}, {date}, ...) |
| Example: | SELECT LEAST(datecol1, datecol2, datecol3) FROM t; |
Oracle LAST_DAY function
Returns the last_day of a month based on the month the passed date is in.
| Syntax: | LAST_DAY({date}) |
| Example: | SELECT LAST_DAY(CURRENT_DATE) FROM t; |
Oracle LEAST function
With the least function you can select the earliest date/time in a range of date/times.
| Syntax: | LEAST({date}, {date}, {date}, ...) |
| Example: | SELECT LEAST(datecol1, datecol2, datecol3) FROM t; |
Oracle MONTHS_BETWEEN function
Returns the number of months between two dates. The number is not rounded.
| Syntax: | MONTHS_BETWEEN({latest_date}, {earliest_date}) |
| Example: | SELECT MONTHS_BETWEEN(SYSDATE+365, SYSDATE-365) FROM dual; |
Oracle NEXT_DAY function
Date of next specified date following a date NEXT_DAY(, ) Options are SUN, MON, TUE, WED, THU, FRI, and SAT. This can be dependend on NLS_SETTINGS!
| Syntax: | NEXT_DAY({date}, {day of the week}) |
| Example: | SELECT NEXT_DAY(SYSDATE, 'FRI') FROM dual; |
Oracle ROUND function
Returns date rounded to the unit specified by the format model. If you omit the format, the date is rounded to the nearest day.
| Syntax: | ROUND({date_value}, {format}) |
| Example: | SELECT ROUND(TO_DATE('27-OCT-00'),'YEAR') NEW_YEAR FROM dual; |
Oracle TRUNC function
Convert a date to the date without time (0:00h).
| Syntax: | TRUNC({date_time}) |
| Example: | SELECT TRUNC(SYSDATE); |
Hi,
ReplyDeleteHow would I find the date of the previous Sunday before today?
thanks!
Renvoi toujours le dimanche précédent même si la date du jour (aujourd'hui) est un dimanche.
ReplyDeleteselect to_char((sysdate - to_number(to_char(sysdate,'D'))),'DAY dd/MM/yyyy') from dual ;
Renvoi le dimanche précédent sauf si la date du jour (aujourd'hui) est un dimanche alors il renvoi la date du jour.
select
case
when to_number(to_char(sysdate,'D')) = 7 then to_char(sysdate,'DAY dd/MM/yyyy')
else to_char(
(sysdate
-
to_number(to_char(sysdate,'D'))
),'DAY dd/MM/yyyy'
)
end
from dual;