- DateTime Function in SQL Server
- GETDATE()
- DATEPART()
- DATEDIFF()
- DATENAME()
- DAY()
- MONTH()
- YEAR()
- String Functions
- ASCII()
- CHAR()
- NCHAR()
- DIFFERENCE()
- LEFT()
- RIGHT()
- LTRIM()
- RTRIM()
- REPLACE()
- QUOTNAME()
- REVERSE()
- CHARINDEX()
- PATINDEX()
- LEN()
- STUFF()
- SUBSTRING()
- LOWER/UPPER()
DateTime Function in SQL Server
Below are the most commonly used
DateTime function in SQL Server.
- GETDATE()
- DATEADD()
- DATEPART()
- DATEDIFF()
- DATENAME()
- DAY()
- MONTH()
- YEAR()
GETDATE()
GETDATE() is very common used method which returns exact
date time from the system. It does not accept any parameter. Just call
it like simple function.
Example :
Declare @Date datetime
set @Date = (SELECT GETDATE());
Print @Date
OutPut:
Aug 15 2009 9:04PM
DATEADD()
DATEADD() is used to add or subtract datetime. Its return a new datetime based on the added or subtracted interval.
General Syntax
DATEADD(datepart, number, date)
datepart is the parameter that specifies on which part of the date to return a new value.
Number parameter is used to increment datepart.
Example :
Declare @Date datetime
set @Date = (SELECT GETDATE());
print @Date -- Print Current Date
-- Adding 5 days with Current Date
SELECT DATEADD(day, 5,@Date ) AS NewTime
Output :
Aug 15 2009 9:19PM
NewTime
-----------------------
2009-08-20 21:19:15.170
DATEPART()
DATEPART() is used when we need a part of date or time from a datetime variable. We can use
DATEPART() method only with select command.
Syntax
DATEPART(datepart, date)
Example :
-- Get Only Year
SELECT DATEPART(year, GETDATE()) AS 'Year'
-- Get Only Month
SELECT DATEPART(month, GETDATE()) AS 'Month'
-- Get Only hour
SELECT DATEPART(hour, GETDATE()) AS 'Hour
Output :
Year
-----------
2009
Month
-----------
8
Hour
-----------
21
DATEDIFF()
DATEDIFF() is very common function to find out the difference between two
DateTime elements.
Syntax
DATEDIFF(datepart, startdate, enddate)
Example :
-- Declare Two DateTime Variable
Declare @Date1 datetime
Declare @Date2 datetime
-- Set @Date1 with Current Date
set @Date1 = (SELECT GETDATE());
-- Set @Date2 with 5 days more than @Date1
set @Date2 = (SELECT DATEADD(day, 5,@Date1 ))
-- Get The Date Difference
SELECT DATEDIFF(day, @Date1, @Date2) AS DifferenceOfDay
Output :
DifferenceOfDay
---------------
5
DATENAME()
DATENAME() is very common and most useful function to find out the date name from the datetime value.
Example
-- Get Today
SELECT DATENAME(dw, getdate()) AS 'Today Is'
-- Get Mont name
SELECT DATENAME(month, getdate()) AS 'Month'
Output :
Today Is
------------------------------
Saturday
Month
------------------------------
August
DAY()
DAY() is used to get the day from any date time object.
Example:
SELECT DAY(getdate()) AS 'DAY'
Output :
DAY
-----------
15
MONTH()
SELECT MONTH(getdate()) AS 'Month'
Output :
Month
-----------
8
YEAR()
SELECT YEAR(getdate()) AS 'Year'
Output :
Year
-----------
2009
String Functions
Some of the String Functions comes very handy at times. Let us discuss them one by one.
ASCII()
Returns the ASCII code value of the leftmost character of a character
expression.
Syntax
ASCII ( character_expression )
Arguments: character_expression : Is an expression of the type char or varchar.
- Return Types: Int
Example:
SELECT ASCII('A')
SET TEXTSIZE 0
SET NOCOUNT ON
DECLARE @position int, @string char(15)
SET @position = 1
SET @string = 'The codeProject'
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT ASCII(SUBSTRING(@string, @position, 1)),
CHAR(ASCII(SUBSTRING(@string, @position, 1)))
SET @position = @position + 1
END
SET NOCOUNT OFF
Output:
65
84 T
104 h
101 e
and so on.....
CHAR()
Converts an
int ASCII code to a character.
Syntax
CHAR ( integer_expression )
Arguments: integer_expression: Is an integer from 0 through 255. NULL is returned if the integer expression is not in this range.
Return Types: character
Example:
SET TEXTSIZE 0
SET NOCOUNT ON
DECLARE @intCounter int
SET @intCounter = 0
WHILE (@intCounter<= 255)
BEGIN
SELECT 'CHAR - ' + CHAR(@intCounter) + '. ASCII - ' + CONVERT(VARCHAR,@intCounter)
SET @intCounter = @intCounter + 1
END
SET NOCOUNT OFF
Output:
CHAR - !. ASCII - 33
CHAR - ". ASCII - 34
------------------------------------------------
CHAR - #. ASCII - 35
------------------------------------------------
CHAR - $. ASCII - 36
------------------------------------------------
CHAR - %. ASCII - 37
------------------------------------------------
CHAR - &. ASCII - 38
------------------------------------------------
CHAR - '. ASCII - 39
------------------------------------------------
CHAR - (. ASCII - 40
------------------------------------------------
and so on.....
NCHAR()
Return a unicode character representing a number passed as a parameter.
Syntax
NCHAR ( integer_expression )
Return Types: character
Example :
SELECT NCHAR(97)
OutPut
This will return the leter "a"
DIFFERENCE()
Returns an integer value that indicates the difference between the SOUNDEX
values of two character expressions.
Syntax
DIFFERENCE ( character_expression , character_expression )
Arguments:character_expression:
Is an expression of type char or varchar. character_expression can also
be of type text; however, only the first 8,000 bytes are significant.
Return Types: Int
Example :
USE AdventureWorks;
GO
SELECT SOUNDEX('Green'), SOUNDEX('Greene'), DIFFERENCE('Green','Greene');
GO
SELECT SOUNDEX('Blotchet-Halls'), SOUNDEX('Greene'), DIFFERENCE('Blotchet-Halls', 'Greene');
GO
Output:
G650 G650 4
(1 row(s) affected)
B432 G650 0
(1 row(s) affected)
LEFT()
Returns the left most characters of a string.
Syntax
LEFT(string, length)
string
Specifies the string from which to obtain the left-most characters.
length
Specifies the number of characters to obtain.
Example :
SELECT LEFT('Marufuzzaman',5)
OutPut
Maruf
RIGHT()
Returns the right most characters of a string.
Syntax
RIGHT(string, length)
string
Specifies the string from which to obtain the left-most characters.
length
Specifies the number of characters to obtain.
Example :
SELECT RIGHT('Md. Marufuzzaman',12)
OutPut
Marufuzzaman
LTRIM()
Returns a character expression after it removes leading blanks.
Example :
SELECT LTRIM(' Md. Marufuzzaman')
OutPut
Md. Marufuzzaman
RTRIM()
Returns a character string after truncating all trailing blanks.
Example :
SELECT RTRIM('Md. Marufuzzaman ')
OutPut
Md. Marufuzzaman
REPLACE()
Returns a string with all the instances of a substring replaced by another
substring.
Syntax
REPLACE(find, replace, string)
Find
Specifies the string that contains the substring to replace all instances of with another.
Replace
Specifies the substring to locate.
String
Specifies the substring with which to replace the located substring.
Example :
SELECT REPLACE('The codeProject is ?.','?', 'your development resource')
OutPut:
The codeProject is your development resource.
QUOTNAME()
Returns a Unicode string with the delimiters added to make the input string a
valid Microsoft SQL Server delimited identifier.
Syntax
QUOTENAME ( 'character_string' [ , 'quote_character' ] )
Arguments
' character_string '
Is
a string of Unicode character data. character_string is sysname and is
limited to 128 characters. Inputs greater than 128 characters return
NULL.
' quote_character '
Is a one-character string to use as
the delimiter. Can be a single quotation mark ( ' ), a left or right
bracket ( [ ] ), or a double quotation mark ( " ). If quote_character is
not specified, brackets are used.
Return Types:
nvarchar(258)
Examples :
The
following example takes the character string abc[]def and uses the [
and ] characters to create a valid SQL Server delimited identifier.
SELECT QUOTENAME('abc[]def')
OutPut:
[abc[]]def]
REVERSE()
Returns a character expression in reverse order.
Example :
SELECT REVERSE('namazzufuraM .dM')
Output:
Md. Marufuzzaman
CHARINDEX
CharIndex returns the first occurance of a string or characters within another string. The Format of CharIndex is given Below:
CHARINDEX
( expression1 , expression2 [ , start_location
] )
Here expression1 is the string of characters to be found within expression2. So if you want to search ij within the word Abhijit, we will use ij as expression1 and Abhijit as expression2. start_location
is an optional integer argument which identifies the position from
where the string will be searched. Now let us look into some examples :
SELECT CHARINDEX('SQL', 'Microsoft SQL Server')
OUTPUT:
11
So
it will start from 1 and go on searching until it finds the total
string element searched, and returns its first position. The Result will
be 0 if the searched string is not found.
We can also mention the Start_Location of the string to be searched.
EXAMPLE:
SELECT CHARINDEX('SQL', 'Microsoft SQL server has a great SQL Engine',12)
So
in the above example we can have the Output as 34 as we specified the
StartLocation as 12, which is greater than initial SQL position(11).
PATINDEX
As a contrast
PatIndex is used to search a pattern within an expression. The Difference between
CharIndex and
PatIndex is the later allows WildCard Characters.
PATINDEX
( '%pattern%' , expression)
Here the first argument takes a pattern with wildcard characters like
'%' (meaning any string) or '_' (meaning any character).
For Example
PATINDEX('%BC%','ABCD')
Output:
2
Another
flexibility of PATINDEX is that you can specify a number of characters
allowed within the Pattern. Say you want to find all of
the records that contain the words "Bread", or "bread" in a string, You
can use the following :
SELECT PATINDEX('%[b,B]read%', 'Tommy loves Bread')
In this example, we mentioned both b and B in square brackets. The Result will be 13 which is same if we have searched in
'Tommy loves bread'.
LEN
Len
is a function which returns the length of a string. This is the most
common and simplest function that everyone use. Len Function excludes
trailing blank spaces.
SELECT LEN('ABHISHEK IS WRITING THIS')
This will output 24, it is same when we write
LEN('ABHISHEK IS WRITING THIS ') as LEN doesnt take trailing spaces in count.
STUFF
Stuff
is another TSql Function which is used to delete a specified length of
characters within a string and replace with another set of characters.
The general syntax of STUFF is as below :
STUFF(character_expression1, start, length, character_expression2)Character_Expression1 represents the string in which the stuff is to be applied.
start indicates the starting position of the character in
character_expression1,
length is
the length of characters which need to be replaced.
character_expression2 is the string that will be replaced to the start
position.
Let us take an example :
SELECT STUFF('SQL SERVER is USEFUL',5,6,'DATABASE')
So the result will be :
SQL DATABASE is USEFUL
SUBSTRING
Substring returns the part of the string from a given characterexpression. The general syntax of Substring is as follows :
SUBSTRING(expression, start, length)
Here the function gets the string from start to length. Let us take an example below:
SELECT OUT = SUBSTRING('abcdefgh', 2, 3)
The output will be "bcd".
Note : substring also works on ntext, VARCHAR, CHAR etc.
LOWER / UPPER
Anoter simple but handy function is Lower / UPPER. The will just change case of a string expression. For Example,
SELECT UPPER('this is Lower TEXT')
Output:
THIS IS LOWER TEXT
