Saturday, September 8, 2007

SQL SET ROWCOUNT

SQL SET ROWCOUNT statement gives administrators some control over the execution of "runaway" queries issued by naive users of SQL. This statement stops the execution of a query as soon as a specified number of rows has been retrieved. In this way, system resources are not wasted, but users can see at least a partial result set. An informational message is displayed after the result set, indicating that SET ROWCOUNT is in effect.
If a fully executed query happens to return the exact number of rows specified by the ROWCOUNT value, the query-termination message is still displayed.

Example of SQL SET ROWCOUNT
SELECT * FROM EMPLOYEES
- It will return 10 rows of records.

After add SET ROWCOUNT
SET ROWCOUNT 5
SELECT * FROM EMPLOYEES
- It will return 5 rows of records instead of 10 row.
(Bacause you set the RowCount to 5)

SQL SET ROWCOUNT

Thursday, September 6, 2007

SQL Split Function

This SQL Split Function is use to SPLIT a sentences based on the Delimeter.
Delimeter is a string character used to identify substring limits.

Below is Split Function in SQL
DECLARE @NextString NVARCHAR(40)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @String NVARCHAR(40)
DECLARE @Delimiter NVARCHAR(40)

SET @String ='SQL,TUTORIALS'
SET @Delimiter = ','
SET @String = @String + @Delimiter
SET @Pos = charindex(@Delimiter,@String)

WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
SELECT @NextString -- Show Results
SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delimiter,@String)
END


Result
- SQL
- TUTORIALS

* Copy blue color Sql split function with change @String variable to test the result in your query analyzer.

SQL Split Function

Sunday, July 8, 2007

SQL Alternative Count

Both SQL Query below returns same result but the speed of retrieving data are different.
Copy Below Both Sql Query to Query analyzer to test it. Customers is a Table Name.

1. SELECT rows FROM sysindexes WHERE id = OBJECT_ID('Customers') AND indid <>
You can use sysindexes system table to get number of ROWS. This column contains the total row count for each table in your database. So, you can use the following select statement instead of Below one:


2. SELECT Count(*) FROM Customers
This SQL query performs full table scan to get the row count.


So, you can improve the speed of the SQL query using the Above First SQL instead of Second SQL Query

SQL Alternative Count

Friday, June 22, 2007

SQL Server DataType

All Sql DataType

bigint
Integer data from -2^63 through 2^63-1

binary
Fixed-length binary data with a maximum length of 8,000 bytes

bit

Integer data with either a 1 or 0 value

char
Fixed-length character data with a maximum length of 8,000 characters

datetime

Date and time data from January 1, 1753, through December 31, 9999,with an accuracy of 3.33 milliseconds

decimal

Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1

float
Floating precision number data from -1.79E + 308 through 1.79E + 308

image

Variable-length binary data with a maximum length of 2^31 - 1 bytes

int
Integer data from -2^31 through 2^31 - 1

money
Monetary data values from -2^63 through 2^63 - 1

nchar
Fixed-length Unicode data with a maximum length of 4,000 characters

ntext
Variable-length Unicode data with a maximum length of 2^30 - 1 characters

nvarchar

Variable-length Unicode data with a maximum length of 4,000 characters

numeric

Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1

real
Floating precision number data from -3.40E + 38 through 3.40E + 38

smalldatetime

Date and time data from January 1, 1900, through June 6, 2079,with an accuracy of one minute

smallint

Integer data from -2^15 through 2^15 - 1

smallmoney
Monetary data values from -214,748.3648 through +214,748.3647

sysname

text

Variable-length data with a maximum length of 2^31 - 1 characters

timestamp

tinyint

Integer data from 0 through 255

varbinary
Variable-length binary data with a maximum length of 8,000 bytes

varchar

Variable-length data with a maximum of 8,000 characters

uniqueidentifier

A globally unique identifier




SQL SET ROWCOUNT
SQL Split Function
SQL Alternative Count
SQL Server DataType
SQL WHILE Statement
SQL CASE Function
SQL IF...ELSE Statement
SQL First and Last Day Of a Month
SQL Statement - get Total Working Day in a month
SQL EXISTS
SQL Subquery
SQL UNION ALL
SQL Union
SQL Mathematic Functions
SQL String Functions
SQL CAST and CONVERT
MySql Total Day of Month
SQL DATEDIFF Function
SQL Dateparts and Abbreviations
SQL DATEADD Function
SQL Statement Last Day of a month
SQL Aggregate Function - Syntax and Example
SQL DATENAME Function - Year, Quarter, Month, Day,...
SQL Inner Join, Left Join and Right Join
Get Number of Row in Select Statement
Select Statement for Get Number of Days in a Month...
SQL Statement Example for SELECT, UPDATE and DELET...
SQL Statement Example for UPDATE and DELETE
All Topic in Sql Tutorials and ProgrammingSchools
SQL Syntax - SELECT,UPDATE and DELETE
What is SQL?

SQL WHILE Statement

SQL While sets a condition for the repeated execution of an SQL statement or statement block. The SQL statements are executed repeatedly as long as the specified condition is return TRUE. The WHILE loop can be controlled from inside the loop with the CONTINUE, BREAK and GOTO keywords.

BREAK statement will exit you from the currently processing WHILE loop.
GOTO statement will break out of all WHILE loops, no matter how many nested WHILE statements.
CONTINUE statement will skips executing the rest of the statements between the CONTINUE statement and the END statement of the current loop and starts executing at the first line of the current WHILE loop.

WHILE Syntax
WHILE Boolean_expression
{ Sql Statement Block }


Below is simple example of WHILE Statement
DECLARE @counter INT
SET @counter = 0
WHILE @counter <>
   BEGIN
      SET @counter = @counter + 1
      PRINT 'The counter : ' + CAST(@counter AS CHAR)
   END
Value :
The counter : 1
The counter : 2
The counter : 3
The counter : 4
The counter : 5


Below is example of WHILE Statement with CONTINUE and BREAK
- It show you the using of Continue and Break in WHILE Statement and the IF...ELSE Statement.
DECLARE @Counter INT
SET @Counter = 0
WHILE @Counter <>
   BEGIN
      SET @Counter = @Counter + 1
      IF @Counter <>
         PRINT 'The counter : ' + CAST(@Counter AS CHAR)
      ELSE IF @Counter > 3 AND @Counter <>
         BEGIN
            CONTINUE
            PRINT 'No Counter Here'
      END
      ELSE IF @Counter > 13 AND @Counter <>
         BREAK
      ELSE
         PRINT 'The counter : ' + CAST(@Counter AS CHAR)
   END
Value :
The counter : 1
The counter : 2
The counter : 3
The counter : 10
The counter : 11
The counter : 12
The counter : 13


Below is example of WHILE Statement with CONTINUE, BREAK and GOTO
- It show you the using of Continue, Break and Goto in WHILE Statement and the IF...ELSE Statement.

DECLARE @N1 INT
DECLARE @N2 INT
SET @N1 = 0
SET @N2 = 0
WHILE @N1 <>
   BEGIN
   SET @N1 = @N1 + 1
      WHILE @N2 <>
         BEGIN
         SET @N2 = @N2 + 1
         IF @N2 = 3 and @N1 = 1
            GOTO BREAK_OUT
         ELSE
            PRINT 'Value N1 is ' + CAST(@N1 AS CHAR(1)) + ' Value N2 is ' + CAST(@N2 AS CHAR(1)) END
         SET @N2 = 0
      END
   BREAK_OUT:
Value :
Value N1 is 1 Value N2 is 1
Value N1 is 1 Value N2 is 2

Thursday, June 21, 2007

SQL CASE Function

SQL Case evaluates a list of conditions and returns one possible result expressions.

CASE has two formats:
1. Simple CASE Function - Compares an expression to determine the result.
2. Searched CASE Function - Evaluates a set of Boolean expressions to determine the result.


CASE Syntax
1. Simple CASE function:
CASE input_expression
WHEN when_expression THEN Result
ELSE result_expression
END

2. Searched CASE function:
CASE
WHEN Boolean_expression THEN Result
ELSE result_expression
END

1. Simple CASE Function
Evaluates input_expression and find the match with when_expression. If found, it will return the Result and if not found, it will return the ELSE result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

DECLARE @Type varchar(20)
SET @Type = 'Programming'

SELECT
   CASE @Type
      WHEN 'Sql' THEN 'sqltutorials.blogspot.com'
      WHEN 'Programming' THEN 'programmingschools.blogspot.com'
      WHEN 'Travel' THEN 'travelyourself.blogspot.com'
      ELSE 'Not yet categorized'
      END
Value = programmingschools.blogspot.com

If SET @Type = 'Picture', then Return value = Not yet categorized



2.Searched CASE Function
Evaluates Boolean_expression for each WHEN clause and returns result_expression of the first Boolean_expression that evaluates to TRUE.
If no Boolean_expression evaluates to TRUE, SQL Server returns the ELSE result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.


DECLARE @Price integer
SET @Price = (20-9)

SELECT
   CASE
      WHEN @Price IS NULL THEN 'Not yet priced'
      WHEN @Price < color="#ff0000">THEN 'Very Reasonable Price'
      WHEN @Price >= 10 AND @Price < color="#ff0000">THEN 'Reasonable Price'
      ELSE 'Expensive book!'
   END
Value = Reasonable Price

If SET @Price = (30-1), then return Value = Expensive book!

SQL IF...ELSE Statement

SQL IF...ELSE Statement used to test a condition. IF...ELSE Statement using in execution of a Transact-SQL statement (Store Procedure or T-SQL) and Trigger.
IF tests can be nested after another IF or following an ELSE. There is no limit to the number of nested levels.


IF condition is satisfied and the Boolean expression returns TRUE, it will executed IF Block Sql statement.
IF condtion is not satisfied and the Boolean expression returns FALSE, it will executed ELSE Block Sql Statement query.

Syntax for IF...ELSE
IF ( Boolean_expression )
BEGIN
Sql Statement Block
END
ELSE
BEGIN
Sql Statement Block
END


Below is simple example of IF...ELSE Statement With 1 IF...ELSE Block
For Boolean_expression part, you can replace with your condition to match with your Sql query.
It also can using Exists in the Condition to check the existence of a Sql Statement or Row.
IF (3>2)
   BEGIN
   SELECT 'Correct'
   END
ELSE
   BEGIN
   SELECT 'Wrong'
   END
Value = Correct

IF (3<2)
   BEGIN
   SELECT 'Correct'
   END
ELSE
   BEGIN
   SELECT 'Wrong'
   END

Value = Wrong

Below is another Example of More than 1 IF...ELSE Sql Statement Block
IF (10<2)
   SELECT 'Correct'
ELSE
   SELECT 'Wrong'
   IF ((10-2) > 5)
      SELECT 'More Than 5'
Value = Wrong
Value = More Than 5

If your IF...ELSE Block Sql Statement only have 1 Sql query, you no need to include the BEGIN...END. If your IF...ELSE Block Sql Statement have more than 1 Sql query, you need to include the BEGIN...END to let it executed all the Sql Query in your Sql Query Block.

Sunday, June 17, 2007

SQL First and Last Day Of a Month

In This SQL Tutorial show you retrieve First Day and Last Day for a month using SQL Query.
It can dynamically to retrieve First Day and Last day for a month using SQL Query.


Example SQL Statement or Query below show you how to retrieve First Day or Last Day for a month.
To display the date in different format, click here.

DECLARE @Today DATETIME
SELECT @Today = '6/17/2007'


Get First Day of a Month Using SQL Query
SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,-3,@Today))

Value = 2007-03-01 00:00:00.000


SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,-2,@Today))
Value = 2007-04-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,-1,@Today))
Value = 2007-05-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,0,@Today))
Value = 2007-06-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,1,@Today))
Value = 2007-07-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,2,@Today))
Value = 2007-08-01 00:00:00.000

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,3,@Today))
Value = 2007-09-01 00:00:00.000


Get Last Day of a Month Using SQL Query
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,-2,@Today))
Value = 2007-03-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,-1,@Today))
Value = 2007-04-30 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,0,@Today))
Value = 2007-05-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,1,@Today))
Value = 2007-06-30 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,2,@Today))
Value = 2007-07-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,3,@Today))
Value = 2007-08-31 00:00:00.000

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,4,@Today))
Value = 2007-09-30 00:00:00.000

Friday, June 15, 2007

SQL Statement - get Total Working Day in a month

SQL Statement below show you how to using SQL to get total working day in a month.
SQL Total working day in this SQL Statement is exclude saturday and sunday as working day.

Below is SQL Statement that get Total Working Day in a month.


DECLARE @my int
DECLARE @myDeduct int
DECLARE @day INT
DECLARE @mydate DATETIME

SET @mydate = getdate()

SET @myDeduct = 0
SET DateFirst 1 -- Set it monday=1 (value)

--Saturday and Sunday on the first and last day of a month will Deduct 1
IF (DATEPART(weekday,(DATEADD(dd,-(DAY(@mydate)-1),@mydate))) > 5)
SET @myDeduct = @myDeduct + 1

IF (DATEPART(weekday,(DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)))) > 5)
SET @myDeduct = @myDeduct + 1

SET @my = day(DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)))

select (((@my/7) * 5 + (@my%7)) - @myDeduct)
as Working_Day_per_month

Return
Value = 21 day (for June 2007)
Value = 22 day (for July 2007)

Sunday, June 10, 2007

SQL EXISTS

Using a subquery to test for the existence of rows.
If it Exists Operators return a row, then the outer query proceeds. If not, the outer query does not execute or return any result or row.

Below is Example for SQL EXISTS

Company

Sales($)

Website

Sql Traniner2500http://www.sqltutorials.blogspot.com
BeautyCentury3000http://beautycentury.blogspot.com
TravelYourself2800http://travelyourself.blogspot.com
Table1

Sales

Website

2900http://www.sqltutorials.blogspot.com
3000http://beautycentury.blogspot.com
2800http://travelyourself.blogspot.com
Table2

SQL EXISTS Statement


SELECT * FROM TABLE1 WHERE EXISTS(SELECT * FROM TABLE2) Result

Company

Sales($)

Website

Sql Traniner2500http://www.sqltutorials.blogspot.com
BeautyCentury3000http://beautycentury.blogspot.com
TravelYourself2800http://travelyourself.blogspot.com



- We also can using the EXISTS in IF...ELSE Statement to checking the existence of a row or value.
- If IF...ELSE return true, it will return the result of Sql statement inside the BEGIN and END.

IF EXISTS(SELECT * FROM TABLE1 WHERE Company='TravelYourself')
BEGIN
SELECT * FROM TABLE1 WHERE Company='TravelYourself'
END

Result
TravelYourself2800http://travelyourself.blogspot.com

Saturday, June 9, 2007

SQL Subquery

Syntax - Subquery
SELECT Statement WHERE ColumnName [Comparison Operator] (SELECT Statement / Values)
Results of one Sql query or Sql statement as the input for another Select query, Insert Into query, Insert From query, Update query and Delete query.
You also can use the result of Subquery as a search condition for using in the IN( ) function or EXISTS operator.

Comparison Operator such as =, >, <, >=, <=, LIKE, IN


Below is example for SQL UNION


Company

Sales($)

Website

Sql Traniner2500http://www.sqltutorials.blogspot.com
BeautyCentury3000http://beautycentury.blogspot.com
TravelYourself2800http://travelyourself.blogspot.com
Table1

Sales

Website

2900http://www.sqltutorials.blogspot.com
3000http://beautycentury.blogspot.com
2800http://travelyourself.blogspot.com
Table2


SQL Subquery Statement

SELECT * FROM TABLE1 WHERE sales in (SELECT Sales FROM TABLE2)
Result
BeautyCentury3000

http://beautycentury.blogspot.com

TravelYourself2800http://travelyourself.blogspot.com



SELECT * FROM TABLE1 WHERE sales LIKE (SELECT Sales FROM TABLE2 WHERE Sales <2900)>
Result

TravelYourself2800http://travelyourself.blogspot.com


SELECT * FROM TABLE1where sales > (SELECT Sales FROM TABLE2 WHERE Sales <2900)
Result
BeautyCentury 3000http://beautycentury.blogspot.com


SQL UNION ALL

SQL UNION ALL is combine the results of two queries together without filter out the same value(no distinct behavior).
The difference between UNION ALL and UNION is UNION only selects distinct values and UNION ALL selects all values.

Syntax - UNION ALL
(SQL Statement 1)
UNION ALL
(SQL Statement 2)

Below is example for SQL UNION

Company

Sales($)

Website

Sql Traniner2500http://www.sqltutorials.blogspot.com
BeautyCentury3000http://beautycentury.blogspot.com
TravelYourself2800http://travelyourself.blogspot.com
Table1

Sales

Website

2900http://www.sqltutorials.blogspot.com
3000http://beautycentury.blogspot.com
2800http://travelyourself.blogspot.com
Table2

SQL UNION ALL Statement
SELECT Sales FROM TABLE1
UNION ALL
SELECT Sales FROM TABLE2

Result
Sales
2500
3000
2800
2900
3000
2800

SQL Union

SQL UNION is to join the results of two queries.
UNION only corresponding columns need to be same data type and when using SQL UNION, only distinct values are selected (is similar to SELECT DISTINCT SQL Statement).


Syntax - UNION
(SQL Statement 1)
UNION
(SQL Statement 2)

Below is example for SQL UNION

Company

Sales($)

Website

Sql Traniner2500http://www.sqltutorials.blogspot.com
BeautyCentury3000http://beautycentury.blogspot.com
TravelYourself2800http://travelyourself.blogspot.com
Table1

Sales

Website

2900http://www.sqltutorials.blogspot.com
3000http://beautycentury.blogspot.com
2800http://travelyourself.blogspot.com
Table2

SQL UNION Statement
SELECT Sales FROM TABLE1
UNION
SELECT Sales FROM TABLE2

Result
Sales
2500
2800
2900
3000

Friday, June 8, 2007

SQL Mathematic Functions

It perform a calculation, usually based on input values and return a numeric value.
Below is All built-in Sql Mathematic Function :
ABS, DEGREES, RAND, ACOS, EXP, ROUND, ASIN, FLOOR, SIGN, ATAN, LOG, SIN, ATN2, LOG10, SQUARE, CEILING, PI, SQRT, COS, POWER, TAN, COT, RADIANS

Example SQL Mathematic Functions - ABS
- Returns the absolute and positive value.
Syntax - ABS ( numeric )
SELECT ABS(-1.0) -- Value = 1.0
SELECT ABS(0.0) -- Value = 0.0
SELECT ABS(1.0) -- Value = 1.0


Example SQL Mathematic Functions - DEGREES
- returns angle in degrees from radian value.
Syntax - DEGREES( numeric)
SELECT DEGREES(1) -- Value = 57 degrees
SELECT DEGREES(2) -- Value = 114 degrees


Example SQL Mathematic Functions - FLOOR
- Returns the largest integer less than or equal to the given numeric expression.
Syntax - FLOOR( numeric)

SELECT FLOOR(32.15) -- Value = 32
SELECT FLOOR(-32.15) -- Value = -33
SELECT FLOOR($32.15) -- Value = 32.0000


Example SQL Mathematic Functions - CEILING
- Returns the smallest integer greater than, or equal to the input value
Syntax - CEILING( numeric)
SELECT CEILING(32.15) -- Value = 33
SELECT CEILING(-32.15) -- Value = -32
SELECT CEILING($32.15) -- Value =
33.0000


Example SQL Mathematic Functions - ROUND
- Return rounded value.
Syntax - ROUND( numeric, length)
SELECT ROUND(32.15 ,1) -- Value = 32.20
SELECT ROUND(32.14 ,1) -- Value = 32.10
SELECT ROUND(32.14998 ,4) -- Value = 32.15000
SELECT ROUND(32.14992 ,4) -- Value = 32.14990
SELECT ROUND(32.92 ,0) -- Value = 33.00


Example SQL Mathematic Functions - POWER
- Returns the value of input to the specified power.
Syntax - POWER( numeric, power(integer))

SELECT POWER(6,2) -- Value = 36 (6*6)
SELECT POWER(9,3) -- Value = 729 (9*9*9)


Thursday, June 7, 2007

SQL String Functions

Sql string function is a built-in string function.
It perform an operation on a string input value and return a string or numeric value.
Below is All built-in Sql string function :
ASCII, NCHAR, SOUNDEX, CHAR, PATINDEX, SPACE, CHARINDEX, REPLACE, STR, DIFFERENCE, QUOTENAME, STUFF, LEFT, REPLICATE, SUBSTRING, LEN, REVERSE, UNICODE, LOWER, RIGHT, UPPER, LTRIM, RTRIM


Example SQL String Function - ASCII
- Returns the ASCII code value of a keyboard button and the rest etc (@,R,9,*) .
Syntax - ASCII ( character)
SELECT ASCII('a') -- Value = 97
SELECT ASCII('b') -- Value = 98
SELECT ASCII('c') -- Value = 99
SELECT ASCII('A') -- Value = 65
SELECT ASCII('B') -- Value = 66
SELECT ASCII('C') -- Value = 67
SELECT ASCII('1') -- Value = 49
SELECT ASCII('2') -- Value = 50
SELECT ASCII('3') -- Value = 51
SELECT ASCII('4') -- Value = 52
SELECT ASCII('5') -- Value = 53



Example SQL String Function - SPACE
-Returns spaces in your SQL query (you can specific the size of space).
Syntax - SPACE ( integer)
SELECT ('SQL') + SPACE(0) + ('TUTORIALS')
-- Value = SQLTUTORIALS
SELECT ('SQL') + SPACE(1) + ('TUTORIALS')
-- Value = SQL TUTORIALS



Example SQL String Function - CHARINDEX
-Returns the starting position of a character string.
Syntax - CHARINDEX ( string1, string2 [ , start_location ] )
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial')

-- Value = 27
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 20)

-- Value = 27
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 30)
-- Value = 0 (Because the index is count from 30 and above)


Example SQL String Function - REPLACE
-Replaces all occurrences of the string2 in the string1 with string3.
Syntax - REPLACE ( 'string1' , 'string2' , 'string3' )
SELECT REPLACE('All Function' , 'All', 'SQL')
-- Value = SQL Function


Example SQL String Function - QUOTENAME
-Returns a Unicode string with the delimiters added to make the input string a valid Microsoft® SQL Server™ delimited identifier.
Syntax - QUOTENAME ( 'string' [ , 'quote_character' ] )
SELECT QUOTENAME('Sql[]String')
-- Value = [Sql[]]String]


Example SQL String Function - STUFF
- Deletes a specified length of characters and inserts string at a specified starting index.
Syntax - STUFF ( string1 , startindex , length , string2 )

SELECT STUFF('SqlTutorial', 4, 6, 'Function')
-- Value = SqlFunctional
SELECT STUFF('GoodMorning', 5, 3, 'good')
-- Value = Goodgoodning


Example SQL String Function - LEFT
-Returns left part of a string with the specified number of characters.
Syntax - LEFT ( string , integer)
SELECT LEFT('TravelYourself', 6)
-- Value = Travel
SELECT LEFT('BeautyCentury',6)
-- Value = Beauty


Example SQL String Function - RIGHT
-Returns right part of a string with the specified number of characters.
Syntax - RIGHT( string , integer)
SELECT RIGHT('TravelYourself', 6)
-- Value = urself
SELECT RIGHT('BeautyCentury',6)
-- Value =
Century


Example SQL String Function - REPLICATE
-Repeats string for a specified number of times.

Syntax - REPLICATE (string, integer)
SELECT REPLICATE('Sql', 2)

-- Value = SqlSql


Example SQL String Function - SUBSTRING
-Returns part of a string.

Syntax - SUBSTRING ( string, startindex , length )
SELECT SUBSTRING('SQLServer', 4, 3)

-- Value = Ser


Example SQL String Function - LEN
-Returns number of characters in a string.
Syntax - LEN( string)
SELECT LEN('SQLServer')
-- Value =
9


Example SQL String Function - REVERSE
-Returns reverse a string.
Syntax - REVERSE( string)
SELECT REVERSE('SQLServer')

-- Value = revreSLQS


Example SQL String Function - UNICODE
-Returns Unicode standard integer value.
Syntax - UNICODE( char)
SELECT UNICODE('SqlServer')
-- Value = 83 (it take first character)
SELECT UNICODE('S')
-- Value =
83


Example SQL String Function - LOWER
-Convert string to lowercase.
Syntax - LOWER( string )
SELECT LOWER('SQLServer')

-- Value = sqlserver


Example SQL String Function - UPPER
-Convert string to Uppercase.
Syntax - UPPER( string )
SELECT UPPER('sqlserver')
-- Value = SQLSERVER


Example SQL String Function - LTRIM
-Returns a string after removing leading blanks on Left side.
Syntax - LTRIM( string )
SELECT LTRIM(' sqlserver')
-- Value = 'sqlserver' (Remove left side space or blanks)



Example SQL String Function - RTRIM
-Returns a string after removing leading blanks on Right side.

Syntax - RTRIM( string )
SELECT RTRIM('SqlServer ')
-- Value = 'SqlServer' (Remove right side space or blanks)

Tuesday, June 5, 2007

SQL CAST and CONVERT

It converts an expression from one data type to another.
CAST and CONVERT have similar functionality.

SQL CAST and CONVERT Syntax
Using CAST:
CAST ( expression AS data_type )


Using CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Example of SQL Cast and Convert

SQL Cast and Convert - String
SELECT SUBSTRING('CAST and CONVERT', 1, 3)
Return Value = CAS (it get from index 1 to 3)

SELECT CAST('CAST and CONVERT' AS char(3))
Return Value = CAS (it get 3 char only)


SQL Cast and Convert - Date Time
-Converting date time to character data(vachar)
-The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the Without century year(yy).
-Add 100 to a style value to get a four-place year that includes the century year(yyyy).
-Below is example for converting 1 format of date time to different format of date time, so that it can be use in various condition.

Value of current Date Time GETDATE()
SELECT (GETDATE()) = 2007-06-06 23:41:10.153


SELECT CONVERT(varchar,GETDATE(),0)
Return Value = Jun 6 2007 11:07PM
SELECT CONVERT(varchar,GETDATE(),100)
Return Value = Jun 6 2007 11:07PM


SELECT CONVERT(varchar,GETDATE(),1)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),101)
Return Value = 06/06/2007


SELECT CONVERT(varchar,GETDATE(),2)
Return Value = 07.06.06
SELECT CONVERT(varchar,GETDATE(),102)
Return Value = 2007.06.06


SELECT CONVERT(varchar,GETDATE(),3)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),103)
Return Value = 06/06/2007


SELECT CONVERT(varchar,GETDATE(),4)
Return Value = 06.06.07
SELECT CONVERT(varchar,GETDATE(),104)
Return Value = 06.06.2007


SELECT CONVERT(varchar,GETDATE(),5)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),105)
Return Value = 06-06-2007


SELECT CONVERT(varchar,GETDATE(),6)
Return Value = 06 Jun 07
SELECT CONVERT(varchar,GETDATE(),106)
Return Value = 06 Jun 2007


SELECT CONVERT(varchar,GETDATE(),7)
Return Value = Jun 06, 07
SELECT CONVERT(varchar,GETDATE(),107)
Return Value = Jun 06, 2007


SELECT CONVERT(varchar,GETDATE(),8)
Return Value = 23:38:49
SELECT CONVERT(varchar,GETDATE(),108)
Return Value = 23:38:49


SELECT CONVERT(varchar,GETDATE(),9)
Return Value = Jun 6 2007 11:39:17:060PM
SELECT CONVERT(varchar,GETDATE(),109)
Return Value = Jun 6 2007 11:39:17:060PM


SELECT CONVERT(varchar,GETDATE(),10)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),110)
Return Value = 06-06-2007


SELECT CONVERT(varchar,GETDATE(),11)
Return Value = 07/06/06
SELECT CONVERT(varchar,GETDATE(),111)
Return Value = 2007/06/06


SELECT CONVERT(varchar,GETDATE(),12)
Return Value = 070606
SELECT CONVERT(varchar,GETDATE(),112)
Return Value = 20070606


SELECT CONVERT(varchar,GETDATE(),13)
Return Value = 06 Jun 2007 23:40:14:577
SELECT CONVERT(varchar,GETDATE(),113)
Return Value = 06 Jun 2007 23:40:14:577


SELECT CONVERT(varchar,GETDATE(),14)
Return Value = 23:40:29:717
SELECT CONVERT(varchar,GETDATE(),114)
Return Value = 23:40:29:717


SELECT CONVERT(varchar,GETDATE(),20)
Return Value = 2007-06-06 23:40:51
SELECT CONVERT(varchar,GETDATE(),120)
Return Value = 2007-06-06 23:40:51


SELECT CONVERT(varchar,GETDATE(),21)
Return Value = 2007-06-06 23:41:10.153
SELECT CONVERT(varchar,GETDATE(),121)
Return Value = 2007-06-06 23:41:10.153


SELECT CONVERT(varchar,GETDATE(),126)
Return Value = 2007-06-06T23:41:10.153


SELECT CONVERT(varchar,GETDATE(),131)
Return Value = 21/05/1428 11:41:10:153PM

MySql Total Day of Month

SQL Statement below is for MySql Database to get the total day of month.
SELECT DAY(LAST_DAY(now()))

Example of SQL Statement to get the total day of month.
SELECT DAY(LAST_DAY('2007-06-05 22:40:51'))
Return Value = 30

I tested MySql Server 5.0, it is work.

SQL DATEDIFF Function

Returns the number of date and time boundaries crossed between two dates

SQL DATEDIFF Syntax
DATEDIFF ( DatePart , StartDate , EndDate )

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-05'

SELECT DATEDIFF(Year, @StartDate, @EndDate) AS NewDate

Return Value = 0 Year


SELECT DATEDIFF(quarter, @StartDate, @EndDate) AS NewDate
Return Value = 1 quarter


SELECT DATEDIFF(Month, @StartDate, @EndDate) AS NewDate
Return Value = 2 Month


SELECT DATEDIFF(dayofyear,@StartDate, @EndDate) AS NewDate
Return Value = 61 day


SELECT DATEDIFF(Day, @StartDate, @EndDate) AS NewDate
Return Value = 61 Day


SELECT DATEDIFF(Week, @StartDate, @EndDate) AS NewDate
Return Value = 9 Week


SELECT DATEDIFF(Hour, @StartDate, @EndDate) AS NewDate
Return Value = 1464 Hour


SELECT DATEDIFF(minute, @StartDate, @EndDate) AS NewDate
Return Value = 87840 minute


SELECT DATEDIFF(second, @StartDate, @EndDate) AS NewDate
Return Value = 5270400 second


DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-06-06'

SELECT DATEDIFF(millisecond, @StartDate, @EndDate) AS NewDate

Return Value = 86400000 millisecond

SQL DATEDIFF Function

SqlTutorials

Monday, June 4, 2007

SQL Dateparts and Abbreviations

Table below lists the dateparts and abbreviations recognized by Microsoft SQL Server(MS SQL).
Datepart below is suitable using in DATEADD, DATEDIFF, DATENAME and all Date related function.

DatepartAbbreviations
Yearyy, yyyy
quarterqq, q
Monthmm, m
dayofyeardy, y
Daydd, d
Weekwk, ww
Hourhh
minutemi, n
secondss, s
millisecondms

SQL DATEADD Function

Returns a new datetime value based on adding an interval to the specified date.

SQL DATEADD Syntax
DATEADD ( datepart , number, date )


DECLARE @DateNow DATETIME
SET @DateNow='2007-06-04'
SELECT DATEADD(Year, 3, @DateNow) AS NewDate

Return Value = 2010-06-04 00:00:00.000


SELECT DATEADD(quarter, 3, @DateNow) AS NewDate
Return Value = 2008-03-04 00:00:00.000


SELECT DATEADD(Month, 3, @DateNow) AS NewDate
Return Value = 2007-09-04 00:00:00.000


SELECT DATEADD(dayofyear,3, @DateNow) AS NewDate
Return Value = 2007-06-07 00:00:00.000


SELECT DATEADD(Day, 3, @DateNow) AS NewDate
Return Value = 2007-06-07 00:00:00.000


SELECT DATEADD(Week, 3, @DateNow) AS NewDate
Return Value = 2007-06-25 00:00:00.000


SELECT DATEADD(Hour, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 03:00:00.000


SELECT DATEADD(minute, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:03:00.000


SELECT DATEADD(second, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:00:03.000


SELECT DATEADD(millisecond, 3, @DateNow) AS NewDate

Return Value = 2007-06-04 00:00:00.003

SQL Statement Last Day of a month

SQL Statement below show the Sql query to get the last day of a month value by using sql DATEADD.
Example of SQL Statement to get last day of a month


DECLARE @DateNow DATETIME
SET @DateNow='2007-07-04'
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@DateNow)), DATEADD(m,1,@DateNow)) AS LastDay

Return Value = 2007-07-31 00:00:00.000



DECLARE @DateNow DATETIME
SET @DateNow='2007-06-04'
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@DateNow)), DATEADD(m,1,@DateNow)) AS LastDay

Return Value = 2007-06-30 00:00:00.000