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.
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
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.
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.
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.
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
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.
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
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.
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.
SyntaxDATENAME ( 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.
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.
SyntaxDATENAME ( 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
Output : December
select datename(year,date)
from tdate
Output : 2003
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.
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
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.
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.
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.
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.
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
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.
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.
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.
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.
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 ( )
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 ( )
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
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
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.
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.
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.
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
Setting the password back to No Password, in other words blank, the default password provided by SQL Server:
EXEC sp_password 'current password', null
you can check the original artice from here
By, Akash Roy, CEO, JPR Infoserve, http://jprinfoserve.com
No comments:
Post a Comment