Date time functions in SQL Server

Date Time Function in SQL Server

List of commonly used data and time functions:
GETDATE()
DATEADD()
DATEPART()
DATEDIFF()
DATENAME()
DAY()
MONTH()
YEAR()

GETDATE()
GETDATE() is very common used method which returns exact date time from the system. It does not used any parameter. Lets see as follows:

SELECT GETDATE()
--------------------------------
Aug 15 2009  9:04PM 

DATEADD()
DATEADD() is used to add or subtract datetime. Its return a new datetime based on the added or subtracted interval.

Declare @Date datetime 
 
set @Date = (SELECT GETDATE());
print  @Date -- Print Current Date
-- Adding 5 days with Current Date
SELECT DATEADD(day, 5,@Date ) AS NewTime
-----------------------------------------------------
Aug 15 2009  9:19PM
NewTime
-----------------------
2009-08-20 21:19:15.170

DATEPART()
Datepart is used when we need a part of date or time from a datetime variable. We can use DATEPART() method only with select command.

DATEPART(datepart, date)

SELECT DATEPART(year, GETDATE()) AS 'Year'
-- Get Only Month
SELECT DATEPART(month, GETDATE()) AS 'Month'
-- Get Only hour
SELECT DATEPART(hour, GETDATE()) AS 'Hour

DATEDIFF()
Datediff is a function to find out the difference between two DateTime elements.

SELECT DATEDIFF(day, @Date1, @Date2) AS DifferenceOfDay

DATENAME()
DATENAME() is a function to find out the date name from the datetime value.

-- Get Today 
SELECT DATENAME(dw, getdate()) AS 'Today Is'
-- Get Mont name
SELECT DATENAME(month, getdate()) AS 'Month'

DAY()
DAY() is used to get the day from any date time object.

select day(getdate()) as 'DAY'

MONTH()
Month is a function used to get the month from the date time object.

SELECT MONTH(getdate()) AS 'Month'

YEAR()
Year is a function used to get the year from the date time object.

SELECT YEAR(getdate()) AS 'Year'

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.