Post
Ask Question

Must-Know SQL Functions For All .NET Developers

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.
  1. SELECT GETDATE()  
  2. GO  
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.
  1. CREATE TABLE Employees  
  2.   
  3. (  
  4. Emp_Id Char(11) NOT NULL,  
  5. Emp_Lname Varchar(40) NOT NULL,  
  6. Emp_Fname Varchar(20) NOT NULL,  
  7. Emp_Hire_date Datetime DEFAULT GETDATE(),  
  8. Emp_Mgr Varchar(30)  
  9. )  
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.
  1. insert into employees (emp_id, emp_lname, emp_fname, emp_mgr)  
  2. values('11111111','agarwal','vidya', 'Mr Komang')   
Current date time 
 
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):
    1. Apr[il] [15][,] 1996
    2. Apr[il] 15[,] [19]96
    3. Apr[il] 1996 [15]
    4. [15] Apr[il][,] 1996
    5. 15 Apr[il][,][19]96
    6. 15 [19]96 apr[il]
    7. [15] 1996 apr[il]
    8. 1996 APR[IL] [15]
    9. 1996 [15] APR[IL]
A Default Date value will be inserted as you have not mentioned a date column in the insert list.
 
time specified format  
 
If you insert a record without specifying the time then it will be 00:00:00 by default.
 
insert date in default 
  1. select * from employees  
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.
 
date inserted in dd mm yyyy format 
 

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
  1. create table tdate  
  2. (  
  3. date datetime  
  4. )  
  5.    
  6. insert into tdate  
  7. values('12-31-2003')  
  8.    
  9. select dateadd(dd,1,date) -- you can also type day at the place of dd  
  10. from tdate  
Output
 
2004-01-01 00:00:00.000
  1. select dateadd(year,10,date)  
  2. 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.
  1. select * from tdate  
Output : 2003-12-31 00:00:00.000 
  1. select dateadd(month,1.75,date)  
  2. 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.
  1. USE pubs  
  2. GO  
  3. SELECT DATEADD(day, 21, pubdate) AS timeframe  
  4. FROM titles  
  5. 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
  1. Select datename(month,date)  
  2. from tdate  
Output : December
  1. select datename(year,date)  
  2. from tdate  
Output : 2003
  1. select datename(day,date)  
  2. 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.
  1. SELECT GETDATE() AS 'Current Date'  
  2. GO  
Here is the result set:
 
Current Date
---------------------------
Feb 18 1998 11:46PM
  1. SELECT DATEPART(month, GETDATE()) AS 'Month Number'  
  2. 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.
  1. 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
  1. select datediff(day,getdate(),date)  
  2. 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.
  1. USE pubs  
  2. GO  
  3. SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days  
  4. FROM titles  
  5. 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.
  1. SELECT DAY('03/12/1998') AS 'Day Number'  
  2. GO  
Here is the result set:
 
Day Number
------------
12 
  1. SELECT DAY(date) AS 'Day Number' --date is column name  
  2. from tdate -- for current date use GETDATE()  
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.
  1. SELECT "Month Number" = MONTH('03/12/1998')  
  2. 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.
  1. SELECT "Year Number" = YEAR('03/12/1998')  
  2. 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.
  1. SELECT "Year Number" = YEAR('03/12/1998')  
  2. 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. 
  1. USE pubs  
  2. GO  
  3. SELECT length = DATALENGTH(pub_name), pub_name  
  4. FROM publishers  
  5. ORDER BY pub_name  
  6. 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 ( )
  1. select app_name()  
Output : Microsoft SQL Server Management Studio - Query
 

HOST_NAME() in SQL Server

 
Returns the workstation name.
 
Syntax
 
HOST_NAME ( )
  1. Select 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
  1. Select 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 
  1. create table idt  
  2. (  
  3. eid int identity,  
  4. name char  
  5. )  
  6. insert into idt  
  7. values('a')  
  8. insert into idt  
  9. values('b')  
  10. 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.
 
  1. -- Create products table.  
  2. CREATE TABLE products  
  3. (  
  4. id int IDENTITY PRIMARY KEY,  
  5. product varchar(40)  
  6. )  
  7. GO  
  8. -- Inserting values into products table.  
  9. INSERT INTO products VALUES ('screwdriver')  
  10. INSERT INTO products VALUES ('hammer')  
  11. INSERT INTO products VALUES ('saw')  
  12. INSERT INTO products VALUES ('shovel')  
  13. GO  
  14. -- Create a gap in the identity values.  
  15. DELETE products -- deleting the row having id 3 i.e. making gap  
  16. WHERE product = 'saw'  
  17. SELECT * FROM products  
  1. -- SET IDENTITY_INSERT to ON.  
  2. SET IDENTITY_INSERT products ON  
  3. -- Attempt to insert an explicit ID value of 3  
  4. INSERT INTO products (id, product) -- if inserting identity value externally must specify  
  5. VALUES(3, 'garden shovel'). -- column names explicitly or externally.  
  6.    
  7. 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. 
  1. 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.
 
  1. EXEC sp_password NULL, 'new Password', 'sa'  
or
  1. EXEC sp_password null, 'new password'

  
Setting the password back to No Password, in other words blank, the default password provided by SQL Server:

 
  1. EXEC sp_password 'current password', null  


Similar Articles