Show Menu
Cheatography

SQL retrieval functions 2 Cheat Sheet by

Date functions

add_­months
(d, n)
Date d plus n months
mont­hs_­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'
time­stamp
'yyyy-mm-dd
hh24:mi:ss.ffffff'
[at time zone '...']
timestamp '2014-­09-23 23:59:­59.9­9999' at time zone 'CET'
time­stamp
'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_n­umber (t)
Convert string t to a number
to_binary_
float
(e[, fmt])
Convert expression e to a floati­ng-­point number
to_binary_
double
(e[, fmt])
Convert expression e to a double­-pr­eci­sion, 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 appear­ances)
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 (abbre­viated 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/m­ont­h/J­ulian day)
Same
day
Closest Sunday
Day name, padded with spaces to length 9
dy, d
Closest Sunday
Day name abbrev­iation (3 charac­ters)
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
/.,
 
Punctu­ation characters
"..."
 
String between double quotes displayed within the date expression
fm
 
Fill mode toggle
th
 
Ordinal number
sp
 
Spell-out number
thsp­,spth
 
Spelle­d-o­rdinal number
The conversion function formats are case-
sensitive.
       

Help Us Go Positive!

We offset our carbon usage with Ecologi. Click the link below to help us!

We offset our carbon footprint via Ecologi
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          SQL retrieval functions 1 Cheat Sheet
          SQLDev KB Shortcuts OS X Cheat Sheet
          Oracle SQL Developer Keyboard Shortcuts

          More Cheat Sheets by chunyan

          SQL retrieval functions 1 Cheat Sheet