santosh-asp tutorial

Date Functions In SQL Server

The Date Function is very usefull in every application. Many programmers are confused, how to use date time function. So, we are going to explain in very simple ways.

As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets complicated.

SQL Server Date Functions:-The are many functions which are following.

S.No.	Function	Description
1.	GETDATE()	Returns the current date and time
2.	DATEPART()	Returns a single part of a date/time
3.	DATEADD()	Adds or subtracts a specified time interval from a date
4.	DATEDIFF()	Returns the time between two dates
5.	CONVERT()	Displays date/time data in different formats

There are several built-in DateTime functions available in SQL Server. All the following functions can be used to get the current system date and time, where you have sql server installed.

GETDATE()----> Commonly used function.
Example-2015-05-18 16:36:18.240
CURRENT_TIMESTAMP----> ANSI SQL equivalent to GETDATE.
Example-2015-05-18 16:36:34.217
SYSDATETIME()----> More fractional seconds precision.
Example-2015-05-18 16:36:48.4096192
SYSDATETIMEOFFSET()----> More fractional seconds precision + Time zone offset.
Example-2015-05-18 16:37:01.6903788 +05:30
GETUTCDATE()----> UTC Date and Time.
Example-2015-05-18 11:07:13.483
SYSUTCDATETIME()----> UTC Date and Time, with More fractional seconds precision.
Example-2015-05-18 11:07:26.8578183

Note:-UTC stands for Coordinated Universal Time, based on which, the world regulates clocks and time. There are slight differences between GMT and UTC, but for most common purposes, UTC is synonymous with GMT.

To practically understand the different date time datatypes available in SQL Server. For this we create follwing table named is tblDateTimeDemo, insert some records and see output as shown in below.

  

CREATE TABLE tblDateTimeDemo
(
CTime time(7)NULL,
CDate date NULL,
Csmalldatetime smalldatetime NULL,
C_Datetime datetime NULL,
C_DATETIME2 datetime2 NULL,
C_DatetimeOffSet datetimeoffset(7) NULL
)


INSERT INTO tblDateTimeDemo VALUES (GETDATE(),GETDATE(),GETDATE(),GETDATE(),GETDATE(),GETDATE())

Explain IsDate, Day, Month, Year and DateName DateTime functions in SQL Server:-

IsDate():--ThisChecks if the given value, is a valid date, time, or datetime. Returns 1 ----->>success, 0 ----->> failure.

Example:-

  

SELECT ISDATE('SANTOSH')---->>RETURN--0

SELECT ISDATE(GETDATE())---->>RETURN--1

SELECT ISDATE('2015-05-18 17:04:36.650')---->>RETURN--1

Note:- For datetime2 values, IsDate returns ZERO.

Example:-

  

SELECT ISDATE('2015-05-18 16:45:00.1330000')---->>RETURN--0

Day():-This function returns the 'Day number of the Month' of the given date.

Example:-

  

SELECT DAY(GETDATE())------->>Return 18


SELECT DAY('2015-05-18 17:45:03.660')------->>Return 18

MONTH():-This function returns the 'Month number of the year' of the given date.

Example:-

  

select MONTH(getdate())------->>Return 5


SELECT MONTH('2015-05-18 17:45:03.660')------->>Return 5

YEAR():-This function returns the 'Year number' of the given date.

Example:-

  

select YEAR(getdate())-------------->>Return 2015


SELECT YEAR('2015-05-18 17:45:03.660')-------------->>Return 2015

DateName(DatePart, Date):-This funtion returns a string, that represents a part of the given date. This functions takes 2 parameters. The first parameter 'DatePart' specifies, the part of the date, we want. The second parameter, is the actual date, from which we want the part of the Date.

Example:-

  

Select DATENAME(YEAR, '2015-05-18 17:45:03.660') ----- Returns 2015

Select DATENAME(MONTH, '2015-05-18 17:45:03.660') ---- Returns May

Select DATENAME(WEEKDAY, '2015-05-18 17:45:03.660') -- Returns Monday

Select DATENAME(Day, '2015-05-18 17:45:03.660') ------ Returns 18

Important DatePart Parameter Values:-

Example:-In this example we use DateTime functions. For this we create following table named tblEmpRecord.

  

CREATE TABLE tblEmpRecord
(
EmpId int primary key,Name varchar(100),Department varchar(300),JoiningDate Datetime
)

----------INSERT SOME RECORDS----------
INSERT INTO  tblEmpRecord VALUES(1001,'Santosh','IT','2011-05-18 17:45:03.660')
INSERT INTO  tblEmpRecord VALUES(1002,'Pooja','CSE','2013-09-16 15:45:08.650')
INSERT INTO  tblEmpRecord VALUES(1003,'Gagan','ECE','2011-10-19 19:25:03.660')
INSERT INTO  tblEmpRecord VALUES(1004,'Anjanee','MAC','2010-12-25 10:45:03.660')
INSERT INTO  tblEmpRecord VALUES(1005,'Suneeta','BCA','2011-05-20 15:15:03.665')

Now, we want write a query, which will return Name, Day, MonthNumber, MonthName,Year and JoiningDate as shown below.

Query is:-

  

SELECT Name,DATENAME(WEEKDAY,JoiningDate)AS DAY,
    MONTH(JoiningDate) AS MonthNumber, 
    DATENAME(MM,JoiningDate) AS MonthName, 
    YEAR(JoiningDate) AS Year, JoiningDate FROM tblEmpRecord

Explain DatePart, DateAdd and DateDiff functions in SQL Server:-

DatePart(DatePart, Date):-This function returns an integer representing the specified DatePart. This function is simialar to DateName(). DateName() returns nvarchar, where as DatePart() returns an integer. The valid DatePart parameter values are shown below.

Example:-

  

Select DATEPART(weekday, '2015-05-19 17:45:03.660') -- returns 3
Select DATENAME(weekday, '2015-05-19 17:45:03.660') -- returns Tuesday

Select DATEPART(MM, '2015-05-19 17:45:03.660') ------- returns 5
Select DATENAME(MM, '2015-05-19 17:45:03.660') ------- returns May

DATEADD (datepart, NumberToAdd, date):-This function returns the DateTime, after adding specified NumberToAdd, to the datepart specified of the given date.

Example:-

  

SELECT DATEADD(DD,100,'2015-05-19 13:03:45.160')------Return 2015-08-27 13:03:45.160
SELECT DATEADD(DD,-100,'2015-05-19 13:03:45.160')-----Return 2015-02-08 13:03:45.160

SELECT DATEADD(MM,100,'2015-05-19 13:03:45.160')------Return 2023-09-19 13:03:45.160
SELECT DATEADD(MM,-100,'2015-05-19 13:03:45.160')-----Return 2007-01-19 13:03:45.160

SELECT DATEADD(YY,100,'2015-05-19 13:03:45.160')------Return 2115-05-19 13:03:45.160
SELECT DATEADD(YY,-100,'2015-05-19 13:03:45.160')-----Return 1915-05-19 13:03:45.160

Now, we explain practical example. For this we create a table named is tblEmpRecord and insert some records as shown in below.

  

CREATE TABLE tblEmpRecord
(
EmpId int primary key,Name varchar(100),Department varchar(300),JoiningDate Datetime
)

----------INSERT SOME RECORDS----------
INSERT INTO  tblEmpRecord VALUES(1001,'Santosh','IT','2011-05-18 17:45:03.660')
INSERT INTO  tblEmpRecord VALUES(1002,'Pooja','CSE','2013-09-16 15:45:08.650')
INSERT INTO  tblEmpRecord VALUES(1003,'Gagan','ECE','2011-10-19 19:25:03.660')
INSERT INTO  tblEmpRecord VALUES(1004,'Anjanee','MAC','2010-12-25 10:45:03.660')
INSERT INTO  tblEmpRecord VALUES(1005,'Suneeta','BCA','2011-05-20 15:15:03.665')

Now, we want get output as shown in below.

Geting the above output, we should write the a query. First of all we have to create Function as shown in below.

  

CREATE FUNCTION fnGetExperience(@JoiningDate DATETIME)
RETURNS NVARCHAR(50)
AS
BEGIN

DECLARE @tempdate DATETIME, @years INT, @months INT, @days INT
SELECT @tempdate = @JoiningDate

SELECT @years = DATEDIFF(YEAR, @tempdate, GETDATE()) - CASE WHEN (MONTH(@JoiningDate) > MONTH(GETDATE())) OR (MONTH(@JoiningDate) = MONTH(GETDATE()) AND DAY(@JoiningDate) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tempdate = DATEADD(YEAR, @years, @tempdate)

SELECT @months = DATEDIFF(MONTH, @tempdate, GETDATE()) - CASE WHEN DAY(@JoiningDate) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tempdate = DATEADD(MONTH, @months, @tempdate)

SELECT @days = DATEDIFF(DAY, @tempdate, GETDATE())

DECLARE @Age NVARCHAR(50)
SET @Age = Cast(@years AS  NVARCHAR(4)) + ' Years ' + Cast(@months AS  NVARCHAR(2))+ ' Months ' +  Cast(@days AS  NVARCHAR(2))+ ' Days Old'
RETURN @Age

End

Now, write the following query and get output as expected in above.

  

SELECT EmpId,Name,JoiningDate,dbo.fnGetExperience(JoiningDate) AS Experience FROM tblEmpRecord