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
Saturday, September 8, 2007
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
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
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?
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
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!
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.
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
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)
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
Table1
Table2
SQL EXISTS Statement
SELECT * FROM TABLE1 WHERE EXISTS(SELECT * FROM TABLE2) Result
- 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
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 Traniner | 2500 | http://www.sqltutorials.blogspot.com |
| BeautyCentury | 3000 | http://beautycentury.blogspot.com |
| TravelYourself | 2800 | http://travelyourself.blogspot.com |
| Sales | Website |
| 2900 | http://www.sqltutorials.blogspot.com |
| 3000 | http://beautycentury.blogspot.com |
| 2800 | http://travelyourself.blogspot.com |
SQL EXISTS Statement
SELECT * FROM TABLE1 WHERE EXISTS(SELECT * FROM TABLE2) Result
Company | Sales($) | Website |
| Sql Traniner | 2500 | http://www.sqltutorials.blogspot.com |
| BeautyCentury | 3000 | http://beautycentury.blogspot.com |
| TravelYourself | 2800 | http://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
| TravelYourself | 2800 | http://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
Table1
Table2
SQL Subquery Statement
SELECT * FROM TABLE1 WHERE sales in (SELECT Sales FROM TABLE2)
Result
SELECT * FROM TABLE1 WHERE sales LIKE (SELECT Sales FROM TABLE2 WHERE Sales <2900)>
Result
SELECT * FROM TABLE1where sales > (SELECT Sales FROM TABLE2 WHERE Sales <2900)
Result
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 Traniner | 2500 | http://www.sqltutorials.blogspot.com |
| BeautyCentury | 3000 | http://beautycentury.blogspot.com |
| TravelYourself | 2800 | http://travelyourself.blogspot.com |
| Sales | Website |
| 2900 | http://www.sqltutorials.blogspot.com |
| 3000 | http://beautycentury.blogspot.com |
| 2800 | http://travelyourself.blogspot.com |
SQL Subquery Statement
SELECT * FROM TABLE1 WHERE sales in (SELECT Sales FROM TABLE2)
Result
| BeautyCentury | 3000 | |
| TravelYourself | 2800 | http://travelyourself.blogspot.com |
SELECT * FROM TABLE1 WHERE sales LIKE (SELECT Sales FROM TABLE2 WHERE Sales <2900)>
Result
| TravelYourself | 2800 | http://travelyourself.blogspot.com |
SELECT * FROM TABLE1where sales > (SELECT Sales FROM TABLE2 WHERE Sales <2900)
Result
| BeautyCentury | 3000 | http://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
Table1
Table2
SQL UNION ALL Statement
SELECT Sales FROM TABLE1
UNION ALL
SELECT Sales FROM TABLE2
Result
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 Traniner | 2500 | http://www.sqltutorials.blogspot.com |
| BeautyCentury | 3000 | http://beautycentury.blogspot.com |
| TravelYourself | 2800 | http://travelyourself.blogspot.com |
| Sales | Website |
| 2900 | http://www.sqltutorials.blogspot.com |
| 3000 | http://beautycentury.blogspot.com |
| 2800 | http://travelyourself.blogspot.com |
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
Table1
Table2
SQL UNION Statement
SELECT Sales FROM TABLE1
UNION
SELECT Sales FROM TABLE2
Result
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 Traniner | 2500 | http://www.sqltutorials.blogspot.com |
| BeautyCentury | 3000 | http://beautycentury.blogspot.com |
| TravelYourself | 2800 | http://travelyourself.blogspot.com |
| Sales | Website |
| 2900 | http://www.sqltutorials.blogspot.com |
| 3000 | http://beautycentury.blogspot.com |
| 2800 | http://travelyourself.blogspot.com |
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)
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)
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
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.
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
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.
Datepart below is suitable using in DATEADD, DATEDIFF, DATENAME and all Date related function.
| 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 |
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 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
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
Subscribe to:
Posts (Atom)