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()

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.

SELECT GETDATE()
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.

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):
  
  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.

 

If you insert a record without specifying the time then it will be 00:00:00 by default.

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.

 

DATEADD()

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) -- you can also type day at the place of dd
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.

select * from tdate

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()

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()

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()

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()

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' --date is column name
from tdate -- for current date use GETDATE()

Here is the result set:

Day Number
------------
31

MONTH()

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()

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()

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()

Returns the application name for the current session if set by the application.

Syntax

APP_NAME ( )

select app_name()

Output : Microsoft SQL Server Management Studio - Query

HOST_NAME()

Returns the workstation name.

Syntax

HOST_NAME ( )

Select host_name()

Output: Will Show your PC Name -- this will show you your workstation name

 

SYSTEM_USER

 

Shows the name of the current system username.

 

Syntax

 

SYSTEM_USER

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 

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



 

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



 

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 products table.
CREATE TABLE products
(
id int IDENTITY PRIMARY KEY,
product varchar(40)
)
GO
-- Inserting values into products table.
INSERT INTO products VALUES ('screwdriver')
INSERT INTO products VALUES ('hammer')
INSERT INTO products VALUES ('saw')
INSERT INTO products VALUES ('shovel')
GO
-- Create a gap in the identity values.
DELETE products -- deleting the row having id 3 i.e. making gap
WHERE product = 'saw'
SELECT * FROM products

-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) -- if inserting identity value externally must specify
VALUES(3, 'garden shovel'). -- column names explicitly or externally.

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



 

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



 

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