This article explains the most commonly used and must-know SQL Server functions and techniques that help and are handy in daily .NET Development and working with SQL Server Database projects. I found that working knowledge of these functions are very helpful and used very often in software development.
Article Covers
- GETDATE()
- DATEADD()
- DATENAME()
- DATEPART()
- DATEDIFF()
- DAY()
- MONTH()
- YEAR()
- DATALENGTH()
- APP_NAME()
- HOST_NAME()
- SYSTEM_USER
- @@IDENTITY
- IDENT_CURRENT
- SET IDENTITY_INSERT
- sp_defaultdb
- Sp_Password
GETDATE() in SQL Server
Returns the current system Date and Time.
Remarks
Date functions can be used in the SELECT statement select list or in the WHERE clause of a query.
Examples
A. Use GET DATE to return the current date and time
This example finds the current system date and time.
Here is the result set:
-------------------------
2003-04-21 13:32:12.293 --the result is in 2 parts date and time
(1 row(s) affected)
B. Use GETDATE with CREATE TABLE
This example creates the employees table and uses GETDATE for a default value for the employee hire date.
- CREATE TABLE Employees
-
- (
- Emp_Id Char(11) NOT NULL,
- Emp_Lname Varchar(40) NOT NULL,
- Emp_Fname Varchar(20) NOT NULL,
- Emp_Hire_date Datetime DEFAULT GETDATE(),
- Emp_Mgr Varchar(30)
- )
The datatype datetime stores dates in the range from January 1, 1753 through December 31, 9999 and it requires 8 bytes of storage per value.
- insert into employees (emp_id, emp_lname, emp_fname, emp_mgr)
- values('11111111','agarwal','vidya', 'Mr Komang')
When you are passing date and time data you can pass it in two ways, either numeric or alphabetic.
These formats just affect the insertion of data, not selection of data. In other words, these formats can only be used to insert data in that manner.
You just need to specify the dateformat, in other words dd-mm-yyyy or mm-dd-yyyy and so on.
Numeric Date Format
Microsoft SQL Server allows you to specify date data with a numeric month specified. For example, 12/09/13 represents the ninth day of December, 2013. When using the numeric date format, specify the month, day, and year in a string with slash marks (/), hyphens (-), or periods (.) as separators. This string must appear in the following form:
number separator number separator number [time] [time]
These numeric formats are valid:
[0]4/15/ 19]96 -- (mdy)
[0]4-15- 19]96 -- (mdy)
[0]4.15. 19]96 -- (mdy)
[04] / [19]96 / 15 -- (myd)
15/[0]4/[19]96 -- (dmy)
15/[19]96/[0]4 -- (dym)
[19]96/15/[0]4 -- (ydm)
[19]96/[04]/15 -- (ymd)
Alphabetic Date Format
Microsoft SQL Server allows you to specify date data with a month specified as the full month name (for example, April) or the month abbreviation (for example, Apr) given in the current language; commas are optional and capitalization (case) is ignored.
Here are some guidelines for the use of alphabetic date formats:
- Enclose the date and time data in single quotation marks (').
- These are the valid alphabetic formats for SQL Server date data (characters enclosed in brackets are optional):
- Apr[il] [15][,] 1996
- Apr[il] 15[,] [19]96
- Apr[il] 1996 [15]
- [15] Apr[il][,] 1996
- 15 Apr[il][,][19]96
- 15 [19]96 apr[il]
- [15] 1996 apr[il]
- 1996 APR[IL] [15]
- 1996 [15] APR[IL]
A Default Date value will be inserted as you have not mentioned a date column in the insert list.
If you insert a record without specifying the time then it will be 00:00:00 by default.
Output
11111111 agarwal vidya 2003-01-31 00:00:00.000 Mr Komang
You can set the format of the order you want to pass the date.
DATEADD() in SQL Server
Returns a new datetime value based on adding an interval to the specified date.
Syntax
DATEADD ( datepart , number, date or column name )
| Datepart |
Abbreviations |
| Year |
yy, yyyy, year |
| Month |
mm, m, month |
| dayofyear |
dy, y, |
| Day |
dd, d, day |
| Week |
wk, ww |
| Hour |
hh |
| minute |
mi, n |
| second |
ss, s |
| millisecond |
ms |
- create table tdate
- (
- date datetime
- )
-
- insert into tdate
- values('12-31-2003')
-
- select dateadd(dd,1,date)
- from tdate
Output
2004-01-01 00:00:00.000
- select dateadd(year,10,date)
- from tdate
The value used to increment the datepart. If you specify this value that is not an integer then the fractional part of the value is discarded or not used. For example, if you specify a day for the datepart and 1.75 for the number, the date is incremented by 1.
Output : 2003-12-31 00:00:00.000
- select dateadd(month,1.75,date)
- from tdate
Output : 2004-01-31 00:00:00.000
Examples
This example prints a listing of a timeframe for titles in the pubs database. This timeframe represents the existing publication date plus 21 days.
- USE pubs
- GO
- SELECT DATEADD(day, 21, pubdate) AS timeframe
- FROM titles
- GO
DATENAME() in SQL Server
Returns a character string representing the specified datepart of the specified date. This works the best only with the datepart MONTH and the rest will produce numeric values.
Syntax
DATENAME ( datepart , date )
Arguments
datepart
Is the parameter that specifies the part of the date to return. The table lists dateparts and abbreviations recognized by Microsoft SQL Server.
| Datepart |
Abbreviations |
| year |
yy, yyyy |
| quarter |
qq, q |
| month |
mm, m |
| dayofyear |
dy, y |
| day |
dd, d |
| week |
wk, ww |
| weekday |
dw |
| hour |
hh |
| minute |
mi, n |
| second |
ss, s |
| millisecond |
ms |
- Select datename(month,date)
- from tdate
Output : December
- select datename(year,date)
- from tdate
Output : 2003
- select datename(day,date)
- from tdate
Output : 31
DATEPART() in SQL Server
Returns an integer representing the specified datepart of the specified date.
Syntax
DATEPART ( datepart , date )
Arguments
datepart
Is the parameter that specifies the part of the date to return. The table lists dateparts and abbreviations recognized by Microsoft SQL Server.
| Datepart |
Abbreviations |
| year |
yy, yyyy |
| quarter |
qq, q |
| month |
mm, m |
| dayofyear |
dy, y |
| day |
dd, d |
| week |
wk, ww |
| weekday |
dw |
| hour |
hh |
| minute |
mi, n |
| second |
ss, s |
| millisecond |
ms |
The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7.
Examples
The GETDATE function returns the current date; however, the complete date is not always the information needed for comparison (often only a portion of the date is compared). This example shows the output of GETDATE as well as DATEPART.
- SELECT GETDATE() AS 'Current Date'
- GO
Here is the result set:
Current Date
---------------------------
Feb 18 1998 11:46PM
- SELECT DATEPART(month, GETDATE()) AS 'Month Number'
- GO
Here is the result set:
Month Number
------------
2
In this example, the date is specified as a number. Notice that SQL Server interprets 0 as January 1, 1900.
- SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)
Here is the result set:
----- ------ ------
1 1 1900
DATEDIFF() in SQL Server
Returns the number of date and time boundaries crossed between two specified dates.
Syntax
DATEDIFF ( datepart , startdate , enddate )
Arguments
datepart
Is the parameter that specifies on which part of the date to calculate the difference. The table lists dateparts and abbreviations recognized by Microsoft SQL Server.
| Datepart |
Abbreviations |
| year |
yy, yyyy |
| quarter |
qq, q |
| month |
mm, m |
| dayofyear |
dy, y |
| day |
dd, d |
| week |
wk, ww |
| weekday |
dw |
| hour |
hh |
| minute |
mi, n |
| second |
ss, s |
| millisecond |
ms |
- select datediff(day,getdate(),date)
- from tdate
Output : 254
Examples
This example determines the difference in days between the current date and the publication date for titles in the pubs database.
- USE pubs
- GO
- SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
- FROM titles
- GO
DAY() in SQL Server
Returns an integer representing the day datepart of the specified date.
Syntax
DAY ( date )
Return Type : int
Remarks
This function is equivalent to DATEPART(dd, date).
Examples
This example returns the number of the day from the date 03/12/1998.
- SELECT DAY('03/12/1998') AS 'Day Number'
- GO
Here is the result set:
Day Number
------------
12
- SELECT DAY(date) AS 'Day Number'
- from tdate
Here is the result set:
Day Number
------------
31
MONTH() in SQL Server
Returns an integer that represents the month part of a specified date.
Syntax
MONTH ( date )
Return Types : int
Remarks
MONTH is equivalent to DATEPART(mm, date).
Examples
This example returns the number of the month from the date 03/12/1998.
- SELECT "Month Number" = MONTH('03/12/1998')
- GO
Here is the result set:
Month Number
------------
3
YEAR() in SQL Server
Returns an integer that represents the year part of a specified date.
Syntax
YEAR ( date )
Return Types : int
Remarks
This function is equivalent to DATEPART(yy, date).
Examples
This example returns the number of the year from the date 03/12/1998.
- SELECT "Year Number" = YEAR('03/12/1998')
- GO
Here is the result set:
Year Number
------------
1998
This example specifies the date as a number. Notice that Microsoft SQL Server database interprets 0 as January 1, 1900.
- SELECT "Year Number" = YEAR('03/12/1998')
- GO
Here is the result set:
----- ------ ------
1 1 1900
DATALENGTH() in SQL Server
Returns the number of bytes used to represent any expression.
Syntax
DATALENGTH ( expression )
Arguments
expression
Is an expression of any type.
Return Types : int
Remarks
DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.
The DATALENGTH of NULL is NULL.
Examples
This example finds the length of the pub_name column in the publishers table.
- USE pubs
- GO
- SELECT length = DATALENGTH(pub_name), pub_name
- FROM publishers
- ORDER BY pub_name
- GO
Here is the result set:
If you analyse the result then each character, along with space represented by one byte, this is the specification that one character occupies one byte in memory.
Try to count the characters in the pub_name and match that with the length given on left.
length pub_name
----------- ----------------------------------------
20 Algodata Infosystems
16 Binnet & Hardley
21 Five Lakes Publishing
5 GGG&G
18 Lucerne Publishing
14 New Moon Books
17 Ramona Publishers
14 Scootney Books
(8 row(s) affected)
APP_NAME() in SQL Server
Returns the application name for the current session if set by the application.
Syntax
APP_NAME ( )
Output : Microsoft SQL Server Management Studio - Query
HOST_NAME() in SQL Server
Returns the workstation name.
Syntax
HOST_NAME ( )
Output: Will Show your PC Name -- this will show you your workstation name
SYSTEM_USER in SQL Server
Shows the name of the current system username.
Syntax
SYSTEM_USER
Output: Will show the UserName used to log-in to SQL Server using Windows Authentication or SQL Authentication.
@@IDENTITY
Returns the last-inserted identity value.
This function is normally useful when we are working with a table having an identity column and we need to know the value available for the last row.
Syntax
@@IDENTITY
- create table idt
- (
- eid int identity,
- name char
- )
- insert into idt
- values('a')
- insert into idt
- values('b')
- select @@identity
Output: 2 -- since you have inserted 2 rows so last identity value must be 2
IDENT_CURRENT
in SQL Server
Returns the last identity value generated for a specified table in any session and any scope.
Select @@identity works well for the current table we are working on, but if we need to know the last value of the identity column in a specific table then IDENT_CURRENT can be used.
Syntax
IDENT_CURRENT ( 'table_name' )
Arguments: table_name
Is the name of the table whose identity value will be returned. table_name is varchar, with no default.
select ident_current ('idt') -- you can specify any table name consisting of Identity column
Output: 2
SET IDENTITY_INSERT
in SQL Server
Allows explicit values to be inserted into the identity column of a table.
Generally it is not allowed to insert data into the Identity Column but in some situations it is possible using this statement. The situation could be to fill a gap in the identity values caused by a DELETE statement.
Syntax
SET IDENTITY_INSERT table ON | OFF
Arguments
table
Is the name of a table with an identity column.
Remarks
At any time, only one table in a session (in other words one query window) can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table then Microsoft SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for. Then to use it for another table you must set that off.
If the value inserted is larger than the current identity value for the table then SQL Server automatically continues by using new inserted value as the current identity value.
The setting of SET IDENTITY_INSERT is set at execution or run time and not at parse time.
Examples
This example creates a table with an identity column and shows how the SET IDENTITY_INSERT setting can be used to fill a gap in the identity values caused by a DELETE statement.
-
- CREATE TABLE products
- (
- id int IDENTITY PRIMARY KEY,
- product varchar(40)
- )
- GO
-
- INSERT INTO products VALUES ('screwdriver')
- INSERT INTO products VALUES ('hammer')
- INSERT INTO products VALUES ('saw')
- INSERT INTO products VALUES ('shovel')
- GO
-
- DELETE products
- WHERE product = 'saw'
- SELECT * FROM products
-
- SET IDENTITY_INSERT products ON
-
- INSERT INTO products (id, product)
- VALUES(3, 'garden shovel').
-
- SELECT * FROM products
Whenever you want, that IDENTITY column's value must be inserted by Microsoft SQL Server, to set it off, SET IDENTITY_INSERT Table_Name OFF
Try this yourself.
If the identity column of a table does not have a Primary key constraint then by using:
SET IDENTITY_INSERT you can insert duplicate values for the IDENTITY Column.
sp_defaultdb
in SQL Server
Changes the default database for a login.
When a client connects with SQL Server then the default database defined for its login becomes the current database without an explicit USE statement.
The master database is the default database if a database is not specified.
After sp_defaultdb is executed, the login is connected to the new database the next time the user logs in.
- EXEC sp_defaultdb 'sa', 'pubs'
Sp_Password
in SQL Server
Change the password of a login.
The default password for SQL Server is a blank that is represented by NULL in the context of the Sp_Password Stored Procedure.
- EXEC sp_password NULL, 'new Password', 'sa'
or
- EXEC sp_password null, 'new password'
Setting the password back to No Password, in other words blank, the default password provided by SQL Server:
- EXEC sp_password 'current password', null
Dinesh GabhanePosted Nov 8, 2019, 1:07 AM
Good One. Thanks
Pankaj Kumar ChoudharyPosted Jul 13, 2016, 4:09 AM
Nice Collcetion Of Queries.........
SubashPosted Jul 7, 2016, 12:35 AM
Thank You Sir
kalu singh raoPosted Jul 1, 2016, 2:02 AM
Nice...
Manish Kumar ChoudharyPosted Jun 28, 2016, 8:18 AM
Nice one
Santhakumar MunuswamyPosted Jun 25, 2016, 2:57 AM
Nice share
Humayun Kabir MamunPosted Jun 18, 2016, 12:46 PM
Nice...
Bhuvanesh MohankumarPosted Jun 18, 2016, 8:35 AM
Nice and great
Saurabh SarkarPosted Jun 17, 2016, 2:52 AM
Nice one...
Bhavik PatelPosted Jun 15, 2016, 10:21 PM
good share
Thiruppathi RPosted Jun 15, 2016, 1:23 AM
Thanks for the great sharing..
Kuppurasu NagarajPosted Jun 14, 2016, 1:12 PM
Nice Sharing..
Navratna PawalePosted Jun 14, 2016, 5:07 AM
nice..
Asad AliPosted Jun 14, 2016, 4:03 AM
good one
Ankur MistryPosted Jun 14, 2016, 2:53 AM
Thanks for sharing.
Vishal PatelPosted Jun 14, 2016, 2:24 AM
Very nice article.. always helpful... informative...Thanks for sharing... keep us updated
Yashwant VishwakarmaPosted Jun 14, 2016, 2:03 AM
Awesome one , Thanks for sharing!!
Vithal WadjePosted Jan 11, 2014, 1:30 PM
best article,keep it up sir
Asif KhanPosted Jan 10, 2014, 5:55 AM
nice article
Sourabh SomaniPosted Dec 27, 2013, 8:13 PM
Very Nice Article....
Girish SapariyaPosted Dec 23, 2013, 5:35 AM
Nice Knowledgable article, something to brushup the mind
Arun TPosted Dec 20, 2013, 1:19 AM
Good Article ... Thx for sharing
Yogendra SolankiPosted Dec 19, 2013, 11:59 PM
ITS VERY IMPROTANT....
Srinubabu RavillaPosted Dec 17, 2013, 12:22 AM
Hi Vidya Vrat Agarwal garu thanks for sharing..... You are right sir every developer must know about all the function you explained above.
Srihari ChinnaPosted Dec 16, 2013, 2:50 AM
Wow!! all in one place now. Thanks
Pramod LawatePosted Dec 13, 2013, 8:34 AM
awesome article sir thanks a lot