ARTICLE

Must-Know SQL Functions For All .NET Developers

Posted by Vidya Vrat Agarwal Articles | SQL Server December 13, 2013
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.
Reader Level:

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

 

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

 

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

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 MONTH(0), DAY(0), YEAR(0)

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

warning

 

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

COMMENT USING

Trending up