MS SQL Server – Functions

some where clause…

or isnull(dateTimeField,’1900-01-01′) !=
isnull(dateTimeField2,’1900-01-01′)

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()

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

forloop_end:

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

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

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

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.

Returns a character expression after converting uppercase character data to
lowercase.

Syntax

varchar = LOWER ( character_expression )

select lower(‘Hello World’) — hello world

Trims the leading blanks from the left side.

Syntax

varchar = LTRIM ( character_expression )

select ‘|’ + ltrim(‘ Hello World ‘ ) + ‘|’ –
|Hello World |see also: rtrim

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

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.

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…>

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

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) — *-*-*-*-*-

Returns the reverse of a character expression.

Syntax

varchar = REVERSE ( character_expression )

select reverse(‘Hello’) –olleH

select reverse(‘Hello World’) — dlroW olleH

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

Trims the blanks from the right side.

Syntax

varchar = RTRIM ( character_expression )

select ‘|’ + rtrim(‘Hello ‘) + ‘|’ — |Hello|

select ‘|’ + rtrim(‘ Hello’) + ‘|’ — | Hello|

see also: ltrim

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

Returns a string of repeated spaces.

Syntax

char = SPACE ( integer_expression )

select ‘|’ + space(5) + ‘|’ — returns: |
| (has 2 spaces)

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|

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

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’

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

Converts lowercase to uppercase.

Syntax

varchar = UPPER ( character_expression )

select upper(‘Hello World’) — HELLO WORLD

– 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

• DateTimeStampreplace(replace(replace(convert(nvarchar(255),getDate(),20),’-',”),’:',”),’
  ‘,’_')

  returns: 20090702_083448  — format: YYYYMMDD_HHMMSS

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

Arguments:

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

– 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:

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

(see DATEPART for int)

Syntax

nvarchar = DATENAME (
datepart , date )

Arguments:

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′)

(see DATENAME for string)Syntax

int = DATEPART ( datepart
, date )

Arguments:

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′)

Syntax

DAY ( date )

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

select DAY ( ’2006-12-31 12:59:59.001′ ) — 31

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()

Syntax

int = MONTH ( date )

select MONTH ( ’2006-12-31 12:59:59.001′ ) — 12

Syntax

int = YEAR ( date )

(Note: equivalent to DATEPART(yy, date).)select YEAR ( ’2006-12-31
12:59:59.001′ ) — 2006

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()

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

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.

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.

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

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

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

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

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

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

Syntax

float = EXP ( float_expression )

select exp ( 123.123456789 ) — 2.9637248139167939E+53

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

Syntax

float = LOG ( float_expression )select LOG ( 1.00 ) — 0

select LOG ( 1.5 ) — 0.40546510810816438

Syntax

float = LOG10 ( float_expression )

select LOG10 ( 1.00 ) — 0

select LOG10 ( 1.5 ) — 0.17609125905568124

Syntax

float = PI ( )

select pi() — 3.1415926535897931

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

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

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

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

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

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

Syntax

float = SQUARE ( float_expression )

select SQUARE ( 3 ) — 9.0

select SQUARE ( 9 ) — 81.0

select SQUARE ( 12 ) — 144.0

Syntax

float = SQRT ( float_expression )

select sqrt ( 9 ) — 3.0

select sqrt ( 81 ) — 9.0

select sqrt ( 144 ) — 12.0

Syntax

float = TAN ( float_expression )

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