Programming in MS SQL Server 2012

Date and Time Functions


Function GETDATE
Description Returns the current system date and time in the Microsoft SQL Server standard internal format for datetime values.
Example SELECT GETDATE( )
Result 2014-04-19 03:36:03.770

* * * * *

Function DAY
Description Returns an integer representing the day datepart of the specified date.
Example SELECT DAY(GETDATE( ) )
Result 19

* * * * *

Function MONTH
Description Returns an integer that represents the month part of a specified date.
Example SELECT MONTH(GETDATE( ) )
Result 4

* * * * *

Function YEAR
Description Returns an integer that represents the year part of a specified date.
Example SELECT YEAR(GETDATE( ) )
Result 2014

* * * * *

Function DATEADD
Description Returns a new datetime value based on adding an interval to the specified date.
Example SELECT GETDATE(), DATEADD(Day, 5, GETDATE( ) )
Result 2014-04-19 03:39:00.540 2014-04-24 03:39:00.540
Example SELECT GETDATE(), DATEADD(Month, 5, GETDATE( ) )
Result 2014-04-19 03:41:21.930 2014-09-19 03:41:21.930
Example SELECT GETDATE(), DATEADD(Year, 5, GETDATE( ) )
Result 2014-04-19 03:42:44.880 2019-04-19 03:42:44.880

* * * * *

Function DATEDIFF
Description Returns the number of date and time boundaries crossed between two specified dates.
Example DECLARE @TESTDATE DATETIME
SET @TESTDATE = '12/31/2000'
SELECT DATEDIFF(Day, @TESTDATE, GETDATE( ) )
Result 4857
Example DECLARE @TESTDATE DATETIME
SET @TESTDATE = '12/31/2000'
SELECT DATEDIFF(Month, @TESTDATE, GETDATE( ) )
Result 160
Example DECLARE @TESTDATE DATETIME
SET @TESTDATE = '12/31/2000'
SELECT DATEDIFF(Year, @TESTDATE, GETDATE( ) )
Result 14

* * * * *

Function DATENAME
Description Returns a character string representing the specified datepart of the specified date.
Example SELECT DATENAME(Day, GETDATE( ) )
Result 19
Example SELECT DATENAME(Month, GETDATE( ) )
Result April
Example SELECT DATENAME(Year, GETDATE( ) )
Result 2014

* * * * *

Function DATEPART
Description Returns an integer representing the specified datepart of the specified date.
Example SELECT DATEPART(Day, GETDATE( ) )
Result 19
Example SELECT DATEPART(Month, GETDATE( ) )
Result 4
Example SELECT DATEPART(Year, GETDATE( ) )
Result 2014

* * * * *

Email Your Comment To AUTHOR

Quick Links
Home
About Me
System Analyze Method
Dimensional Data Model
Entity Relationship Model
Relational Database Model
Tutorials
MS SQL Server - DBA
SSIS - Video Tutorials
SSAS - Video Tutorials
SSRS - Video Tutorials
SQL Server White Papers
Programming in SQL Server
Help Us
Contact Me
R.K.Prem Nath
Madurai District
Tamil Nadu, India

[email protected]
[email protected]
Terms Of Use                                                  Protected by Copyscape Online Plagiarism Tool             Copyrights © . Data-e-Education. All Rights Reserved.