MS SQL Server – Functions


  • SQL Help

    • Launch “SQL Query Analyzer” then Help, click “Transact-SQL Help”, click
      the tab “Contents”, then click on the plus sign by “Transact-SQL Reference”.
    • Launch “SQL Query Analyzer” then Help, “Contents and Index”.  When “SQL
      Server Books Online” loads click on “Index” and type the words “functions”
      for the keyword search.  You will see a list of functions.
  • Helpful info
    • SET NOCOUNT ON - turns off the results count from displaying.
      "OFF" is the default.

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′)
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 2See 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
exampleUsing 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

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

  • DateTimeStampreplace(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.137see 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()
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.390541Note:  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.12300Here 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

  • Meet Author

    S.Abhilash, started this blog in the later half of 2009 as a self hosting site for all his previous blogging attempts. , My way of writing and frequency of publishing may not be uniform as my resources are my projects nd issues i came across. For the last few years this blog is reacing 100 s of unique visitors each day, thanks wordpress and google for such a perfection in their software and services.

    Head to Authors Page | Abhilash on Facebook

    Submit Your Email For Free Updates on New Articles



Category MSSQL Server     Tags , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Comment on Facebook

1 Comment to “MS SQL Server – Functions”

  • Anish R Nair from Kochi, Kerala, India May 10, 2010 at 7:31 PM

    The MSSQL functions that describe in this topic is very useful

Community



FREE News Letter

Enter your email address

See Also

Resources

Please Submit your Email Address to Get Latest Technology News As it Happens


Enter your email address


Never see this message again.
Read previous post:
MSSQL Server Date Manipulations (datename)

In MSSQL Server its necessary for to see weekname (sunday,monday,etc..) and, weekcount and even some times its important to the day count in an year and most of DBA s directly count these , but SQL has a lot of built in Functions

Close