Cybercrux

Everything is achievable through technology

SQL

SQL is a standard language for accessing databases.

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 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&#8217;,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


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

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

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

Advertisements

5 thoughts on “SQL

  1. beautiful blog with nice informational content. this is a really interesting and informative post. good job! keep it up, hope to read your other updates. thanks for this nice sharing.

  2. I just want to say I’m very new to weblog and definitely loved you’re website. Almost certainly I’m planning to bookmark your blog post . You actually have terrific stories. Kudos for revealing your web-site.

  3. resetting sa Password

    step1: osql –L
    step2: OSQL -S -E
    step3: sp_password NULL, ‘’, ’sa’
    step4: go

  4. remove dependency of forignkey table
    use
    ON UPDATE CASCADE or
    ON DELETE CASCADE

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s