Show Menu

SQL retrieval functions 2 Cheat Sheet by

sql     date     oracle

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.

Download the SQL retrieval functions 2 Cheat Sheet

2 Pages
//media.cheatography.com/storage/thumb/chunyan_sql-retrieval-functions-2.750.jpg

PDF (recommended)

Alternative Downloads

Share This Cheat Sheet!

 

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

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

          More Cheat Sheets by chunyan

          SQL retrieval functions 1 Cheat Sheet