MS SQL Survival Guide - Functions
 (SQL 2000)

Index to this Web Page

SQL Functions

Function Description
Misc Functions  
isnull() isnull(check_expression, replacement_value) - Replaces NULL with with the specified replacement value.  Returns the same type as the check_expression.

Examples:
some where clause...
  or isnull(dateTimeField,'1900-01-01') != isnull(dateTimeField2,'1900-01-01')

 
String Functions  
ascii ASCII
Returns the ASCII code value of the leftmost character of a character expression.

Syntax
int = ASCII ( character_expression )

Ex:
select ascii('ABC') -- 65
select ascii('A') -- 65
select ascii('Z') -- 90
select ascii('a') -- 97
select ascii('z') -- 122
select ascii('0') -- 48
select ascii('9') -- 57
select ascii('_') -- 95
select ascii(' ') -- 32

Related char()
char Char
A string function that converts an int ASCII code to a character.  The argument is an integer from 0 through 255. NULL is returned if the integer expression is not in this range.

Syntax
char(1) = CHAR ( integer_expression )

Examples:

select char(ascii('A'))  -- A
select char(65)  -- A
select char(145) -- Left Slanted single quote: ‘
select char(146) -- Right Slanted single quote: ’

Note:
SQL: char(13) = carriage return, char(10) = line feed.
Similar to VB's:  vbCrLf
Similar to JavaScripts: \n
Example:
Print 'Code list: ' + char(13) + char(10) + 'A = Hello 1' + char(13) + char(10) + 'B = Hello 2'Results:
Results:
Code list:
A = Hello 1
B = Hello 2

See also:
select nchar(65) -- A
select unicode('A') -- 65

To my knowledge, the loop constructs are designed to work only in User Defined functions and in DTS packages.
Let me know if you know otherwise.

-- Note:
-- 1. Normally looping is done in a user defined function.
-- 2. Here is a make shift way in straight SQL without a user defined function.

declare @strValue varchar(255)
declare @intCount int
declare @intCountMax int

set @intCount = 0
set @intCountMax = 255

forloop_begin:

  set @strValue = (select (convert(varchar(15),@intCount) + '-' + char( @intCount ) ))
  print @strValue

  set @intCount = @intCount + 1

  if ( @intCount <= @intCountMax ) begin
    goto forloop_begin
  end

forloop_end:
charindex CHARINDEX
Returns the starting position of the specified expression in a character string.
0=Not found. 1=1st Character position.

Syntax
int = CHARINDEX ( expression1 , expression2 [ , start_location ] )

expression1 - search for
expression2 - search in.
Start_location = 0 or negative = beginning.

Ex:
select charindex( 'World', 'Hello World Again')  -- 7
select charindex( '/', 'http://michael-thomas.com/tech/mssql/index.htm',27) -- 31
 
difference DIFFERENCE
Returns the difference between the SOUNDEX values of two character expressions as an integer.  The integer returned is the number of characters in the SOUNDEX values that are the same. The return value ranges from 0 through 4, with 4 indicating the SOUNDEX values are identical.

Note:  SOUNDEX() returns a 4 char value.  DIFFERENCE() will tell you how many of those characters are different.
DIFFERENCE() evaluates the difference in the similarity of 2 strings.  I'm not sure how to use this function in a real applications.  I welcome any comments.

Syntax
int = DIFFERENCE ( character_expression , character_expression )

Example:

SELECT SOUNDEX('Hello') -- H400
DIFFERENCE('Hello','Hello') -- 4

SELECT SOUNDEX('Mississippi') - M221
select DIFFERENCE('Mississippi','Mississippi') -- 4

SELECT SOUNDEX('Jimmy') -- J500
SELECT SOUNDEX('Jimmie') -- J500
select DIFFERENCE('Jimmy','Jimmie') -- 4

SELECT SOUNDEX('abcdefg') -- A123
SELECT SOUNDEX('tuvwxyz') -- T122
select DIFFERENCE('abcdefg','tuvwxyz') -- 2  (1 "1" + 1 "2" = 2)

SELECT SOUNDEX('dog') -- D200
SELECT SOUNDEX('dot') -- D300
select DIFFERENCE('dog','dot') -- 3  (1 "D" and 2 "0"s = 3)

select SOUNDEX('Bob') -- B100
select SOUNDEX('Suzie') -- S200
select DIFFERENCE('Bob','Suzie') -- 2  (There are 2 occurrences of "0" in the SOUNDEX value..)

All numbers have the same SOUNDEX value therefore 4 is returned.
select SOUNDEX('1') -- 0000
select SOUNDEX('2') -- 0000
select SOUNDEX('20') -- 0000
select SOUNDEX('100') -- 0000
select DIFFERENCE('1','2') -- 4
select DIFFERENCE('20','100') -- 4
 
left LEFT
Returns the part of a character string starting at a specified number of characters from the left.

Syntax
varchar = LEFT ( character_expression , integer_expression )

Example:
select left('Hello World',3) -- Hel
 
len LEN
Returns the number of characters of the given string expression, excluding trailing blanks.

Syntax
int = LEN ( string_expression )

Example:
select len('Hello World') -- 11
select len('Hello World     ') -- 11  Note: excludes the trailing blanks.
lower LOWER
Returns a character expression after converting uppercase character data to lowercase.

Syntax
varchar = LOWER ( character_expression )

select lower('Hello World') -- hello world
ltrim LTRIM
Trims the leading blanks from the left side.

Syntax
varchar = LTRIM ( character_expression )

select '|' + ltrim(' Hello World ' ) + '|' -- |Hello World |

see also: rtrim
nchar NCHAR
Returns the Unicode character with the given integer code, as defined by the Unicode standard.
Argument should be a positive whole number from 0 - 65535 else a NULL is returned.

Syntax
nchar(1) = NCHAR ( integer_expression )

select nchar(65) -- A

See also:
select unicode('A') -- 65
select ascii('A') -- 65
patindex PATINDEX - Returns the starting position of the first occurrence of a pattern within the expression.  Returns 0 if not found.

Syntax
int = PATINDEX ( '%pattern%' , expression )

select patindex('%World%', 'Hello World example') -- 7
select patindex('%ple', 'Hello World example') -- 17
select patindex('Hello%', 'Hello World example') -- 1
select patindex('%Hello', 'Hello World example') -- 0 Note: No wildcard at the end.
replace REPLACE
Replaces the occurrences of the 2nd string with value of the 3rd string where found in the 1st string.

Syntax
nvarchar = REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )

select replace ( 'Hello World example', 'World' , 'play' ) -- Hello play example

Using replace with a field type of 'ntext'.
update <table>
  set myNtext = replace(cast(myNtext as varchar(8000)), 'findme', 'replacewithme')
where
  <table>.id = <id#etc...>
 
quotename QUOTENAME
Returns a valid Microsoft® SQL Server™ delimited identifier.  If the 'quote_character' is not specified, brackets ([ ]) is the default.   Valid 'quote_character' can be: single quotation mark ('), a left or right bracket ([]), or a double quotation mark (").  "NULL" is returned for non-valid 'quote_character'.

Syntax
nvarchar(129) = QUOTENAME ( 'character_string' [ , 'quote_character' ] )

select quotename('Hello World Example') -- [Hello World Example]
select quotename('Hello [World] Example') -- [Hello [World]] Example]
select quotename('Hello World Example', '''') -- 'Hello World Example'
select quotename('Hello World Example', '"') -- "Hello World Example"
select quotename('Michael''s world', '''') -- 'Michael''s world'
select quotename('Hello World Example', '|') -- NULL
 
replicate REPLICATE
Repeats a character expression for a specified number of times.

Syntax
varchar = REPLICATE ( character_expression , integer_expression )

select replicate('*',5) -- *****
select replicate('-',5) -- -----
select replicate('*-',5) -- *-*-*-*-*-
 
reverse REVERSE
Returns the reverse of a character expression.

Syntax
varchar = REVERSE ( character_expression )

select reverse('Hello') --olleH
select reverse('Hello World') -- dlroW olleH
 
right RIGHT
Returns the specified number of chars starting from the right side.

Syntax
varchar = RIGHT ( character_expression , integer_expression )

select right('1234567890',3) --890
select right('Hello World',3) -- rld
rtrim RTRIM
Trims the blanks from the right side.

Syntax
varchar = RTRIM ( character_expression )

select '|' + rtrim('Hello ') + '|' -- |Hello|
select '|' + rtrim(' Hello') + '|' -- | Hello|

see also: ltrim
soundex SOUNDEX
Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.

Syntax
char = SOUNDEX ( character_expression )

select soundex('Hello') -- H400
select soundex('Mississippi') -- M221
select soundex('Jimmy') -- J500
select soundex('Jimmie') -- J500
select soundex('abcdefg') -- A123
select soundex('tuvwxyz') -- T122
select soundex('dog') -- D200
select soundex('dot') -- D300
select soundex('Test1Test') -- T230
select soundex('Test12345678Test') -- T230
-- All numbers have the same SOUNDEX value therefore 4 is returned.
select soundex('1') -- 0000
select soundex('2') -- 0000
select soundex('20') -- 0000
select soundex('100') -- 0000
space SPACE
Returns a string of repeated spaces.

Syntax
char = SPACE ( integer_expression )

select '|' + space(5) + '|' -- returns: | | (has 2 spaces)
str STR
Returns character data converted from numeric data.

Syntax
char = STR ( float_expression [ , length [ , decimal ] ] )

Arguments
  • float_expression - Is an expression of approximate numeric (float) data type with a decimal point. Do not use a function or subquery as the float_expression in the STR function.
  • length (optional) - Is the total length, including decimal point, sign, digits, and spaces. The default is 10.
  • decimal (optional) - Is the number of places to the right of the decimal point.

-- Ex: No length or decimal uses the defaults: 10-length, 0-decimal
select '|' + str(10.5) + '|' -- | 11|
-- Ex: Decimal value of '0' will round the number to a whole number.
select '|' + str(10.5,10,0) + '|' -- | 11|
select '|' + str(10.5,5,0) + '|' -- | 11|
-- Ex: Changing the Length value
select '|' + str(10.5,6,3) + '|' -- |10.500|
select '|' + str(10.5,5,3) + '|' -- |10.50|
select '|' + str(10.5,4,3) + '|' -- |10.5|
select '|' + str(10.5,3,3) + '|' -- | 11|
-- Ex: Changing the Decimal value.
select '|' + STR(123.45, 10, 1) + '|' -- | 123.5|
select '|' + STR(123.45, 10, 2) + '|' -- | 123.45|
select '|' + STR(123.45, 10, 3) + '|' -- | 123.450|
select '|' + STR(123.45, 10, 4) + '|' -- | 123.4500|
 
stuff stuff
Deletes a specified length of characters and inserts another set of characters at a specified starting point.

Syntax:
STUFF( char_exp, start, length, char_exp_to_stuff)

Ex:
select stuff('Hello Again',1,0, 'World')
-- WorldHello Again
select stuff('Hello Again',1,1, 'World')
-- Worldello Again
select stuff('Hello Again',7,0, 'World ')
-- Hello World Again

--Ex: Remove a character
select stuff('Hello*World',charindex('*','Hello*World'),1,'')
-- returns: Hello World

--Ex: Remove a string of characters.
select stuff('Hello<b>World',charindex('<b>','Hello<b>World'),len('<b>'),'')
-- returns: HelloWorld

-- Ex: Return new string if the remove value exits
-- else, return the original string.
declare @strSearch nvarchar(255)
declare @strRemove nvarchar(255)

set @strSearch = 'Hello<b>World'
set @strRemove = '<b>'

select
( case
when ( charindex(@strRemove, @strSearch) > 0 )
then stuff(@strSearch,charindex(@strRemove, @strSearch), len(@strRemove),'')
else @strSearch
end
)
--returns: HelloWorld
substring SUBSTRING
Returns part of an expression starting at a specified position and continuing for a specified length.

Syntax
<see below> = SUBSTRING ( expression , start , length )

Return types:
text -> varchar
image -> varbinary
ntext -> nvarchar
 
Arguments
start - Starting position
length - length to return from the starting position.

select substring('Hello World Again',7,5)  -- 'World'
unicode UNICODE
Returns the integer value, as defined by the Unicode standard, for the first character of the input expression.

Syntax
int = UNICODE ( 'ncharacter_expression' )

unicode:
select unicode( 'A' ) -- 65
select unicode( 'Z' ) -- 90

See also:
select nchar(65) -- A
select ascii('A') -- 65
upper UPPER
Converts lowercase to uppercase.

Syntax
varchar = UPPER ( character_expression )

select upper('Hello World') -- HELLO WORLD
Date / Time Functions  
2 Digit Years 2 digit Years. >= 50 is 19xx (ex: 1950) etc... 49 <= is 20xx (ex: 2049)

-- 2 digit Years. >= 50 is 19xx (ex: 1950) etc... 49 <= is 20xx (ex: 2049)
select datediff ( year , '01/01/49', '01/01/50' ) -- -99
select datediff ( year , '01/01/50', '01/01/51' ) -- 1
select datediff ( year , '01/01/2049', '01/01/2050' ) -- 1
Misc Date/Time Info Misc Date/Time Info
  • DateTimeStamp
    replace(replace(replace(convert(nvarchar(255),getDate(),20),'-',''),':',''),' ','_')
    returns: 20090702_083448  -- format: YYYYMMDD_HHMMSS
     
convert Converting date/time to a character.

convert(<data type>, datetime, style)

Examples:

select convert(varchar(255),getDate())
-- Returns: Jul 2 2009 1:12PM
select convert(varchar(255),getDate(),0)
-- Returns: Jul 2 2009 1:16PM
select convert(varchar(255),getDate(),1)
-- Returns: 07/02/09
select convert(varchar(255),getDate(),2)
-- Returns: 09.07.02
select convert(varchar(255),getDate(),3)
-- Returns: 02/07/09
select convert(varchar(255),getDate(),4)
-- Returns: 02.07.09
select convert(varchar(255),getDate(),5)
-- Returns: 02-07-09
select convert(varchar(255),getDate(),6)
-- Returns: 02 Jul 09
select convert(varchar(255),getDate(),7)
-- Returns: Jul 02, 09
select convert(varchar(255),getDate(),8)
-- Returns: 13:17:43
select convert(varchar(255),getDate(),9)
-- Returns: Jul 2 2009 1:17:58:310PM
select convert(varchar(255),getDate(),10)
-- Returns: 07-02-09
select convert(varchar(255),getDate(),11)
-- Returns: 09/07/02
select convert(varchar(255),getDate(),12)
-- Returns: 090702
select convert(varchar(255),getDate(),13)
-- Returns: 02 Jul 2009 13:18:37:590
select convert(varchar(255),getDate(),14)
-- Returns: 13:18:46:013
select convert(varchar(255),getDate(),20)
-- Returns: 2009-07-02 13:12:23
select convert(varchar(255),getDate(),21)
-- Returns: 2009-07-02 13:18:59.840
select convert(varchar(255),getDate(),126)
-- Returns: 2009-07-02T13:19:10.543
select convert(varchar(255),getDate(),130)
-- Returns: 10 ??? 1430 1:19:22:700PM
select convert(varchar(255),getDate(),131)
-- Returns: 10/07/1430 1:19:37:607PM
 

Without century (yy) With century (yyyy)
Standard
Input/Output**
- 0 or 100 (*) Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - Mon dd, yy
8 108 - hh:mm:ss
- 9 or 109 (*) Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 (*) Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 or 120 (*) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121 (*) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
- 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(no spaces)
- 130* Kuwaiti dd mon yyyy hh:mi:ss:mmmAM
- 131* Kuwaiti dd/mm/yy hh:mi:ss:mmmAM

 
dateadd
dateadd() - Returns a new datetime value (same as the date argument) based on adding an interval to the specified date.

Syntax
<date type> = DATEADD ( datepart , number, date )
Note:
<date type> - based on the 'date' argument.

Arguments:

Datepart Abbreviations
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms

Example of Arguments for Year:
select DATEADD ( year , 5, '2006-01-01 08:00:00.000' )
select DATEADD ( yyyy , 5, '2006-01-01 08:00:00.000' )
select DATEADD ( yy , 5, '2006-01-01 08:00:00.000' )

-- 2 digit Years. >= 50 is 19xx (ex: 1950) etc... 49 <= is 20xx (ex: 2049)
select DATEADD ( year , 10, '01/01/49' ) -- 2059-01-01 00:00:00.000
select DATEADD ( year , 10, '01/01/50' ) -- 1960-01-01 00:00:00.000 Seen as 1950 and not 2050!
select DATEADD ( year , 10, '01/01/2050' ) -- 2060-01-01 00:00:00.000

--Example of the different date parts.
select DATEADD ( yy , 1, '2006-01-01 08:00:00.000' ) -- 2007-01-01 08:00:00.000
select DATEADD ( qq , 1, '2006-01-01 08:00:00.000' ) -- 2006-04-01 08:00:00.000
select DATEADD ( mm , 1, '2006-01-01 08:00:00.000' ) -- 2006-02-01 08:00:00.000
select DATEADD ( dy , 1, '2006-01-01 08:00:00.000' ) -- 2006-02-01 08:00:00.000
select DATEADD ( dd , 1, '2006-01-01 08:00:00.000' ) -- 2006-02-01 08:00:00.000
select DATEADD ( ww , 1, '2006-01-01 08:00:00.000' ) -- 2006-01-08 08:00:00.000
select DATEADD ( hh , 1, '2006-01-01 08:00:00.000' ) -- 2006-01-01 09:00:00.000
select DATEADD ( mi , 1, '2006-01-01 08:00:00.000' ) -- 2006-01-01 08:01:00.000
select DATEADD ( ss , 1, '2006-01-01 08:00:00.000' ) -- 2006-01-01 08:00:01.000
select DATEADD ( ms , 500, '2006-01-01 08:00:00.000' ) -- 2006-01-01 08:00:00.500

-- Example: Today's date (Add, Sub)
select getdate() as 'now', dateadd(dd,-1,getdate()) as 'Subtract 1 day', dateadd(dd,1,getdate()) as 'Add 1 day'
-- 2007-10-18 10:42:25.513 2007-10-17 10:42:25.513 2007-10-19 10:42:25.513
select getdate() 'now', dateadd(hh,-1,getdate()) as 'Sub 1 hour', dateadd(hh,1,getdate()) as 'Add 1 hour'
--2008-01-02 13:00:45.967 2008-01-02 12:00:45.967 2008-01-02 14:00:45.967
(Note: for UTC time use: getutcdate() )

-- Warning: Milliseconds seems to have issues:
select DATEADD ( ms , 1, '2006-01-01 08:00:00.000' ) -- 2006-01-01 08:00:00.000
select DATEADD ( ms , 2, '2006-01-01 08:00:00.000' ) -- 2006-01-01 08:00:00.003
select DATEADD ( ms , 3, '2006-01-01 08:00:00.000' ) -- 2006-01-01 08:00:00.003
select DATEADD ( ms , 4, '2006-01-01 08:00:00.000' ) -- 2006-01-01 08:00:00.003
select DATEADD ( ms , 5, '2006-01-01 08:00:00.000' ) -- 2006-01-01 08:00:00.007
select DATEADD ( ms , 10, '2006-01-01 08:00:00.000' ) -- 2006-01-01 08:00:00.010
datediff datediff - returns the difference of 2 dates based on the "datepart" argument.
-- yy or yyyy - Returns the difference of year units based on the Year values (doesn't look at the Month or Day)
-- q or qq - Returns the difference of quarter units based on the Month & Year values (doesn't look at the Day).
-- m or mm - Returns the difference of month units based on the Month & Year (doesn't look at the day)

Syntax
int = DATEDIFF ( datepart , startdate , enddate )

Max for milliseconds: 24 days, 20 hours, 31 minutes and 23.647 seconds.
Max for seconds: 68 years.

Arguments:

Datepart Abbreviations
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms

Example of Arguments for Year:
select datediff(year, '12/31/2000', '01/01/2001') -- 1 - only 1 day, but returns 1.
select datediff(yy, '12/31/2000', '01/01/2001') -- 1 - only 1 day, but returns 1.
select datediff(yyyy, '12/31/2000', '01/01/2001') -- 1 - only 1 day, but returns 1.

-- 2 digit Years. >= 50 is 19xx (ex: 1950) etc... 49 <= is 20xx (ex: 2049)
select datediff ( year , '01/01/49', '01/01/50' ) -- -99
select datediff ( year , '01/01/50', '01/01/51' ) -- 1
select datediff ( year , '01/01/2049', '01/01/2050' ) -- 1

-- yy - Returns the difference of year units based on the Year values (doesn't look at the Month or Day)
select datediff(yy, '12/31/2000', '01/01/2001') -- 1 - only 1 day, but returns 1.
select datediff(yy, '01/01/2000', '12/31/2000') -- 0 - 364 days, but returns 0.
select datediff(yy, '01/01/2000', '01/01/2010') -- 10
select datediff(yy, '01/01/1900', '06/01/2010') -- 110
select datediff(yy, '01/01/2000', '12/31/1999') -- -1
select datediff(yy, '01/01/2000', '12/31/1900') -- -100

-- q - Returns the difference of quarter units based on the Month & Year values (doesn't look at the Day).
select datediff(q, '01/01/2000', '01/01/2002') -- 8
select datediff(q, '01/01/2000', '03/31/2000') -- 0
select datediff(q, '01/01/2000', '04/01/2000') -- 1
select datediff(q, '01/31/2000', '04/01/2000') -- 1
select datediff(q, '01/01/2000', '07/01/2000') -- 2
select datediff(q, '01/01/2000', '12/31/2000') -- 3
select datediff(q, '01/01/2000', '01/01/2001') -- 4
select datediff(q, '01/31/2000', '01/01/2001') -- 4
select datediff(q, '01/01/2001', '01/01/2000') -- -4
select datediff(q, '01/01/2002', '01/01/2000') -- -8

-- mm - Returns the difference of month units based on the Month & Year (doesn't look at the day)
select datediff(mm, '12/31/2000', '01/01/2001') -- 1 - only 1 day, but returns 1.
select datediff(mm, '11/30/2000', '12/01/2000') -- 1 - only 1 day, but returns 1.
select datediff(mm, '01/01/2000', '01/01/2001') -- 12
select datediff(mm, '01/01/2000', '01/01/2002') -- 24
select datediff(mm, '01/01/2001', '01/01/2000') -- -12

-- dy
select datediff(dy, '12/31/2000', '01/01/2001') -- 1
select datediff(dy, '01/01/2000', '12/31/2000') -- 365
select datediff(dy, '01/01/2000', '12/31/1999') -- -1
select datediff(dy, '01/01/2000', '12/31/2000') -- 365
select datediff(dy, '01/01/2000', '01/01/2001') -- 366

select datediff(dy, '02/01/2000', '03/01/2000') -- 29
select datediff(dy, '02/01/2001', '03/01/2001') -- 28
select datediff(dy, '02/01/2002', '03/01/2002') -- 28
select datediff(dy, '02/01/2003', '03/01/2003') -- 28
select datediff(dy, '02/01/2004', '03/01/2004') -- 29

-- d
select datediff(dd, '12/31/2000', '01/01/2001') -- 1
select datediff(dd, '01/01/2000', '12/31/2000') -- 365
select datediff(dd, '01/01/2000', '12/31/1999') -- -1

-- ww
select datediff(ww, '2006-01-01 08:00:00.000' , '2006-01-14 08:00:00.000' ) -- 1
select datediff(ww, '2006-01-01 08:00:00.000' , '2006-01-15 08:00:00.000' ) -- 2
-- hh
select datediff(hh, '2006-01-01 08:00:00.000' , '2006-01-01 09:00:00.000' ) -- 1
-- mi, n
select datediff(mi, '2006-01-01 08:00:00.000' , '2006-01-01 08:01:00.000' ) -- 1
-- ss, s
select datediff(ss, '2006-01-01 08:00:00.000' , '2006-01-01 08:00:01.000' ) -- 1
-- ms  (Warning:  Milliseconds have issues!!!)
select datediff(ms, '2006-01-01 08:00:00.000' , '2006-01-01 08:00:00.001' ) -- 0 (Warning: Issue !!!)
select datediff(ms, '2006-01-01 08:00:00.000' , '2006-01-01 08:00:00.500' ) -- 500
 
datename DATENAME - Returns a character string representing the specified datepart of the specified date.
(see DATEPART for int)

Syntax
nvarchar = DATENAME ( datepart , date )

Arguments:
Datepart Abbreviations
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms

select DATENAME ( yy , '2006-12-31 12:59:59.001' ) -- 2006
select DATENAME ( qq , '2006-12-31 12:59:59.001' ) -- 4
select DATENAME ( mm , '2006-12-31 12:59:59.001' ) -- December (see datepart for a int)
select DATENAME ( dy , '2006-12-31 12:59:59.001' ) -- 365
select DATENAME ( dd , '2006-12-31 12:59:59.001' ) -- 31
select DATENAME ( ww , '2006-12-31 12:59:59.001' ) -- 53
select DATENAME ( hh , '2006-12-31 08:00:00.001' ) -- 8 (24hr clock!)
select DATENAME ( hh , '2006-12-31 12:00:00.001' ) -- 12 (24hr clock!)
select DATENAME ( hh , '2006-12-31 17:00:00.001' ) -- 17 (24hr clock!)
select DATENAME ( mi , '2006-12-31 12:59:59.001' ) -- 59
select DATENAME ( ss , '2006-12-31 12:59:59.001' ) -- 59
select DATENAME ( ms , '2006-12-31 12:59:59.001' ) -- 0 (Warning: Expected '001')
 
datepart DATEPART - Returns an integer representing the specified datepart of the specified date.
(see DATENAME for string)

Syntax
int = DATEPART ( datepart , date )

Arguments:

Datepart Abbreviations
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms

select DATEPART ( yy , '2006-12-31 12:59:59.001' ) -- 2006
select DATEPART ( qq , '2006-12-31 12:59:59.001' ) -- 4
select DATEPART ( mm , '2006-12-31 12:59:59.001' ) -- 12
select DATEPART ( dy , '2006-12-31 12:59:59.001' ) -- 365
select DATEPART ( dd , '2006-12-31 12:59:59.001' ) -- 31
select DATEPART ( ww , '2006-12-31 12:59:59.001' ) -- 53
select DATEPART ( hh , '2006-12-31 12:59:59.001' ) -- 12
select DATEPART ( hh , '2006-12-31 08:00:00.001' ) -- 8 (24hr clock!)
select DATEPART ( hh , '2006-12-31 12:00:00.001' ) -- 12 (24hr clock!)
select DATEPART ( hh , '2006-12-31 17:00:00.001' ) -- 17 (24hr clock!)
select DATEPART ( mi , '2006-12-31 12:59:59.001' ) -- 59
select DATEPART ( ss , '2006-12-31 12:59:59.001' ) -- 59
select DATEPART ( ms , '2006-12-31 12:59:59.001' ) -- 0 (Warning: Expected '001')
 
day DAY - Returns an integer representing the day of the specified date.

Syntax
DAY ( date )
(Note: equivalent to DATEPART(dd, date))

select DAY ( '2006-12-31 12:59:59.001' ) -- 31
getdate GETDATE
Returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values.

Syntax
datetime = GETDATE ( )
(Note: equivalent to DATEPART(mm, date).)

select GETDATE ( ) -- 2006-04-24 13:01:12.137

see also: getutcdate()
month MONTH - Returns an integer that represents the month part of a specified date.

Syntax
int = MONTH ( date )

select MONTH ( '2006-12-31 12:59:59.001' ) -- 12
year YEAR - Returns an integer that represents the year part of a specified date.

Syntax
int = YEAR ( date )
(Note: equivalent to DATEPART(yy, date).)

select YEAR ( '2006-12-31 12:59:59.001' ) -- 2006
 
getutcdate GETUTCDATE - Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running.

Syntax
datetime = GETUTCDATE()

Remarks - from "MSSQL Transact SQL Reference"
GETUTCDATE is a nondeterministic function. Views and expressions that reference this column cannot be indexed.
GETUTCDATE cannot be called inside a user-defined function.

My notes:
Very handy because of time zones.

select GETUTCDATE() -- 2006-04-24 17:13:03.513 ( 5:13pm GMT )
select GETDATE() -- 2006-04-24 13:13:03.513 ( 1:13pm EST )

Time Zones:
select getdate() – date/time of the server’s timezone.
select getutcdate() – GMT
select dateadd(hh,-5,getutcdate()) – if server is in the EST, then this is the same as getdate()
select dateadd(hh,-4,getutcdate()) – if server is in the EDT, then this is the same as getdate()
 

see also: getdate()
Math Functions  
abs ABS
Returns the absolute, positive value of the given numeric expression.

Syntax
<Same type> = ABS ( numeric_expression )

select abs(-1) -- 1
select abs(-1.0) -- 1.0
select abs (-1.5) -- 1.5
acos ACOS
Returns the angle, in radians, whose cosine is the given float expression; also called arccosine.

Syntax
float = ACOS ( float_expression )

Arguments
float_expression - float or real with a value from -1 through 1.

select acos(-1) -- 3.1415926535897931
select acos(1) -- 0.0
select acos(.015) -- 1.5557957642379359
select acos(-1.5) -- A domain error occurred.
asin ASIN - Returns the angle, in radians, whose sine is the given float expression (also called arcsine).

Syntax
float = ASIN ( float_expression )

Argument: Values from -1 through 1

select asin(1.00) -- 1.5707963267948966
select asin(-1.00)-- 1.5707963267948966
select asin(0.99) -- 1.4292568534704693
select asin(-0.99) -- -1.4292568534704693
select asin(1.01) -- Messages: A domain error occurred.
select asin(-1.01) -- Messages: A domain error occurred.
atan ATAN - Returns the angle in radians whose tangent is the given float expression (also called arctangent).

Syntax
float = ATAN ( float_expression )

select atan(360) -- 1.568018556161576
select atan(180) -- 1.568018556161576
select atan(90) -- 1.5652408283942041
select atan(45) -- 1.5596856728972892
select atan(0) -- 0.0
select atan(-1) -- -0.78539816339744828
select atan(900) -- 1.568018556161576
 
atn2 ATN2 - Returns the angle, in radians, whose tangent is between the two given float expressions (also called arctangent).

Syntax
float = ATN2 ( float_expression , float_expression )

select atn2(360, 180) -- 1.1071487177940904
select atn2(180, 90) -- 1.1071487177940904
select atn2(360, 0) -- 1.5707963267948966
select atn2(360, 90) -- 1.3258176636680326
ceiling CEILING - Returns the smallest integer greater than, or equal to, the given numeric expression.

Syntax
<same as argument> = CEILING ( numeric_expression )

See also:  floor

select ceiling ( 100.00 ) -- 100
select ceiling ( 100.01 ) -- 101
select ceiling ( 100.50 ) -- 101
select ceiling ( 100.40 ) -- 101
select ceiling ( -1 ) -- -1
select ceiling ( -1.01 ) -- -1
select ceiling ( -1.50 ) -- -1
select ceiling ( -1.99 ) -- -1
cos COS - A mathematic function that returns the trigonometric cosine of the given angle (in radians) in the given expression.

Syntax
float = COS ( float_expression )

select cos(360) -- -0.28369109148652732
select cos(180) -- -0.59846006905785809
select cos(90) -- -0.44807361612917013
select cos(45) -- 0.52532198881772973
select cos(0) -- 1.0
select cos(-1) -- 0.54030230586813977
select cos(900) -- 0.06624670220315812
cot COT - A mathematic function that returns the trigonometric cotangent of the specified angle (in radians) in the given float expression.

Syntax
float = COT ( float_expression )

select cot(360) -- -0.29584569796855498
select cot(180) -- 0.74699881441404437
select cot(90) -- -0.50120278338015323
select cot(45) -- 0.61736962378355509
select cot(0) -- 1.0 -- A domain error occurred.
select cot(-1) -- -0.64209261593433076
select cot(900) -- 6.6392548412446309E-2
degrees DEGREES - Returns the corresponding angle in degrees for a given an angle in radians,

Syntax
<same as argument> = DEGREES ( numeric_expression )

select degrees(radians(45)) -- 0
select degrees(radians(-45)) -- 0
select degrees(radians(90)) -- 57
select degrees(radians(-90)) -- -57
 
exp EXP - Returns the exponential value of the given float expression.

Syntax
float = EXP ( float_expression )

select exp ( 123.123456789 ) -- 2.9637248139167939E+53
 
floor FLOOR - Returns the largest integer less than or equal to the given numeric expression.

Syntax
<same as argument> = FLOOR ( numeric_expression )

select floor ( 100.00 ) -- 100
select floor ( 100.01 ) -- 100
select floor ( 100.50 ) -- 100
select floor ( 100.99 ) -- 100
select floor ( -1 ) -- -1
select floor ( -1.01 ) -- -2
select floor ( -1.50 ) -- -2
select floor ( -1.99 ) -- -2
log LOG - Returns the natural logarithm of the given float expression.

Syntax
float = LOG ( float_expression )

select LOG ( 1.00 ) -- 0
select LOG ( 1.5 ) -- 0.40546510810816438
log10 LOG10 - Returns the base-10 logarithm of the given float expression.

Syntax
float = LOG10 ( float_expression )

select LOG10 ( 1.00 ) -- 0
select LOG10 ( 1.5 ) -- 0.17609125905568124
pi PI - Returns the constant value of PI.

Syntax
float = PI ( )

select pi() -- 3.1415926535897931
power POWER
Returns the value of the given expression to the specified power.

Syntax
<same as 1st arg> = POWER ( numeric_expression , y )
y - Is the power to which to raise numeric_expression. y can be an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

select power ( 100 , 1 ) -- 100
select power ( 100 , 2 ) -- 10000
select power ( 100 , 3 ) -- 1000000
select power ( 3 , 1 ) -- 3
select power ( 3 , 2 ) -- 9
select power ( 3 , 3 ) -- 27
radians RADIANS - Returns radians when a numeric expression, in degrees, is entered.

Syntax
<numeric_expression> = RADIANS ( numeric_expression )

select radians(45) -- 0
select radians(-45) -- 0
select radians(90) -- 1
select radians(-90) -- -1
select radians(180) -- 3
select radians(-180) -- -3
select radians(360) -- 6
select radians(-360) -- -6
rand RAND - Returns a random float value from 0 through 1.
Warning:  I don't like the way this function works.  I find it hard to return a random # from 1-10 (or 1-100 etc...) with this function.

Syntax
float = RAND ( [ seed ] )
seed - integer expression like: int, tinyint, smallint

SET NOCOUNT ON
--Look at the Messages Tab
select RAND ( 1 ) -- 0.71359199321292355
select RAND ( 1 ) -- 0.71359199321292355
select RAND ( -1 ) -- 0.71359199321292355
select RAND ( 1000 ) -- 0.73220633149986536
select RAND ( -1000 ) -- 0.73220633149986536

print RAND ( 1 ) -- 0.713592 (Value is truncated)
print RAND ( 1 ) -- 0.713592 (Value is truncated)

DECLARE @count int
DECLARE @seed int
DECLARE @multiplier int
SET @count = 1
SET @multiplier = 100
WHILE @count < 10
BEGIN
set @seed = @count * @multiplier
print 'Count=' + convert(nvarchar, @seed) + ', Rand=' + convert(nvarchar, RAND(@seed * 100))
SET @count = @count + 1
END
SET NOCOUNT OFF
GO

/*
Results: (Will be the same everytime it is run.)
Count=100, Rand=0.899903
Count=200, Rand=0.0862328
Count=300, Rand=0.272563
Count=400, Rand=0.458892
Count=500, Rand=0.645222
Count=600, Rand=0.831552
Count=700, Rand=0.0178814
Count=800, Rand=0.204211
Count=900, Rand=0.390541

Note:  If the multiplier is 1 then the following results:
(Warning: Notice that the values are in the 0.7xxx range.)
Count=1, Rand=0.715437
Count=2, Rand=0.7173
Count=3, Rand=0.719163
Count=4, Rand=0.721027
Count=5, Rand=0.72289
Count=6, Rand=0.724753
Count=7, Rand=0.726616
Count=8, Rand=0.72848
Count=9, Rand=0.730343

*/

-- WARNING - DATETIME seeds:  If you execute the random #s from the current datetime you will get duplicates because the values change at the millisecond level.

Date Time Seed Examples

  • -- This code can still generate duplicates because it can be executed in the same millisecond.
    select rand(convert(int, DATEPART(ms, GETDATE()))*1000)
  • -- This code can still generate duplicates because it can be executed in the same millisecond.
    select rand( ( convert(float, getdate()) - convert(int, getdate()) ) * 1000000000 )
  • -- This code can still generate duplicates because it can be executed in the same millisecond.
    -- WARNING:  SQL Help uses this example.  It only produces #s in the range of .7xxxx.
    SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
    + (DATEPART(ss, GETDATE()) * 1000 )
    + DATEPART(ms, GETDATE()) )

Random #s from 1 to x:

  • -- Example of a random # from 1 to 10 (See warning about datetime seed.)
    select round(rand(convert(int, DATEPART(ms, GETDATE()))*1000) * 10, 0)
  • -- Example of a random # from 1 to 100 (See warning about datetime seed.)
    select round(rand(convert(int, DATEPART(ms, GETDATE()))*1000) * 100, 0)

Example of the DateTime Warning:

declare @count int
set @count = 1
WHILE @count < 5
BEGIN
print 'Count=' + convert(nvarchar, @count) + ', Rand=' + convert(nvarchar, round(rand(convert(int, DATEPART(ms, GETDATE()))*1000) * 10, 0))
SET @count = @count + 1
END
GO

Results:
Count=1, Rand=8
Count=2, Rand=8
Count=3, Rand=8
Count=4, Rand=8
round ROUND - Returns a numeric expression, rounded to the specified length or precision.

Syntax
<same as arg> = ROUND ( numeric_expression , length [ , function ] )
function - If 0 normal rounding.  If not 0, then truncate #'s past the length and then round.

select round ( 100.4 , 0 ) -- 100.0
select round ( 100.5 , 0 ) -- 101.0
select round ( 100.49 , 0 ) -- 100.0

select round ( 100.49 , 1 ) -- 100.50
select round ( 100.449 , 1 ) -- 100.400

-- Example of the "function" to truncate, then round.
select round ( 100.49 , 1, 0 ) -- 100.50
select round ( 100.49 , 1, 1 ) -- 100.40 (.4 vs .49 - A value other than 0 will truncate values past the round #. )

select round ( 100.12345 , 0 ) -- 100.00000
select round ( 100.12345 , 1 ) -- 100.10000
select round ( 100.12345 , 2 ) -- 100.12000
select round ( 100.12345 , 3 ) -- 100.12300

Here is some weird ones:

select convert(decimal,140813)/1000/60 -- returns 2.346883333
select round((convert(decimal,140813)/1000/60),1) -- returns 2.300000000, should be 2.4

select convert(float,140813)/1000/60 -- returns 2.346883333333333
select round((convert(float,140813)/1000/60),1) -- returns 2.2999999999999998, should be 2.4
select round((140813/1000/60),1) -- returns 2, should be 2.4
select round((140813/1000/60),2) -- returns 2, should be 2.35

select (140813.0/1000.0/60.0) -- returns 2.34688333333
select round( (140813.0/1000.0/60.0), 1) -- returns 2.30000000000, should be 2.4
select round( (140813.0/1000.0/60.0), 2) -- returns 2.35000000000
 
sign SIGN - Returns the positive (+1), zero (0), or negative (-1) sign of the given expression.

Syntax
float = SIGN ( numeric_expression )

select sign ( 0 ) -- 0
select sign ( 1 ) -- 1
select sign ( -1 ) -- -1
select sign ( 100 ) -- 1
select sign ( -100 ) -- -1
select sign ( 55 ) -- 1
select sign ( -55 ) -- -1
select sign ( 2 ) -- 1
select sign ( -2 ) -- -1
 
sin SIN - Returns the trigonometric sine of the given angle (in radians) in an approximate numeric (float) expression.

Syntax
float = SIN ( float_expression )

select SIN ( 45 ) -- 0.85090352453411844
select SIN ( 90 ) -- 0.89399666360055785
select SIN ( 180 ) -- -0.80115263573383044
select SIN ( 360 ) -- 0.95891572341430653
square SQUARE - Returns the square of the given expression.

Syntax
float = SQUARE ( float_expression )

select SQUARE ( 3 ) -- 9.0
select SQUARE ( 9 ) -- 81.0
select SQUARE ( 12 ) -- 144.0
sqrt SQRT - Returns the square root of the given expression.

Syntax
float = SQRT ( float_expression )

select sqrt ( 9 ) -- 3.0
select sqrt ( 81 ) -- 9.0
select sqrt ( 144 ) -- 12.0
tan TAN - Returns the tangent of the input expression.

Syntax
float = TAN ( float_expression )

select tan(pi()/2) -- 1.6331778728383844E+16