Date functions
add_months
(d, n) |
Date d plus n months |
months_between
(d, e) |
Months between dates d and e (d-e) |
last_day (d) |
Last day of the month containing date d |
next_day
(d, weekday) |
the first weekday (mon, etc.) after d |
new_time
(d, z1, z2) |
Convert date/time from time zone z1 to z2 |
round
(d[, fmt]) |
d rounded on fmt; defualt midnitht |
trunc
(d[, fmt]) |
d truncated on fmt; defualt: midnight |
extract
(c from d) |
Extract date/time component c from expression d |
General functions
greatest (a, b, ...) |
Greatest value |
least
(a, b, ...) |
Least value |
nullif
(a, b) |
NULL if a=b; otherwise a |
coalesce (a, b, ...) |
The first NOT NULL argument; NULL if all are NULL |
NVL (x, y) |
When x is NULL then y; else x |
NVL2
(x, y, z) |
When x is not NULL then y, else z |
Case x
...
end |
when a1 then b1
when a2 then b2...
else y |
decode (x,
a1, b1,
a2, b2
...
[, y]) |
when x=a1 then b1
when x=a2 then b2
else y (or default: NULL) |
|
|
Date functions: constants
date
'yyyy-mm-dd' |
date '2018-01-25' |
timestamp
'yyyy-mm-dd
hh24:mi:ss.ffffff'
[at time zone '...'] |
timestamp '2014-09-23 23:59:59.99999' at time zone 'CET' |
timestamp
'yyyy-mm-dd
hh24:mi:ss.ffffff
{+|-}hh:mi' |
timestamp '2014-08-21 23:59:59.99 -5:00' |
interval 'expr'
<qualifier> |
interval '1' year; interval '1 2:3' day to minute |
Sample function
sample (p) |
p: percentage |
seed (n) |
n: No. of the seed |
Conversion functions
to_char
(n[,fmt]) |
Convert number n to a string |
to_char
(d[,fmt]) |
Convert date/time expression d to a string |
to_number (t) |
Convert string t to a number |
to_binary_
float
(e[, fmt]) |
Convert expression e to a floating-point number |
to_binary_
double
(e[, fmt]) |
Convert expression e to a double-precision, floating-
point number |
to_date
(t[, fmt]) |
Convert string t to a date |
to_
yminterval
(t) |
Convert string to a YEAR TO MONTH interval |
to_
dsinterval
(t) |
Convert string t to a DAY TO SECOND interval |
to_
timestamp
(t[, fmt]) |
Convert string to a timestamp |
cast
(e as t) |
Convert expression e to datatype t |
"fmt' for date functions and conversion functions
cc, scc |
Century, with or without minus sign (BC) |
Same |
[s]yyyy, [s]year, yyy, yy, y |
Year (in various appearances) |
Same |
iyyyy, iyy, iy, i |
ISO year |
Same |
bc, ad |
|
BC/AC indicator |
q |
Quarter |
Same |
month |
Month (full name) |
Month name, padded with spaces to length 9 |
mon, mm, rm |
Month (abbreviated name, numeric, Roman numerals) |
Same |
iw, ww |
(ISO) week number |
Same |
w |
Day of the week |
Week number within the month |
ddd, dd, j |
Day (of the year/month/Julian day) |
Same |
day |
Closest Sunday |
Day name, padded with spaces to length 9 |
dy, d |
Closest Sunday |
Day name abbreviation (3 characters) |
d |
Closest Sunday |
Day number within the week |
am, pm |
|
AM/PM indicator |
hh,hh12, hh24 |
Hours |
Same |
mi |
Minutes |
Same |
ss |
|
Seconds |
sssss |
|
Seconds after midnight |
/., |
|
Punctuation characters |
"..." |
|
String between double quotes displayed within the date expression |
fm |
|
Fill mode toggle |
th |
|
Ordinal number |
sp |
|
Spell-out number |
thsp,spth |
|
Spelled-ordinal number |
The conversion function formats are case-
sensitive.
|
Created By
Metadata
Comments
No comments yet. Add yours below!
Add a Comment
Related Cheat Sheets
More Cheat Sheets by chunyan