As a follow up to our article “The Most Useful Date and Time Functions in Oracle Database”, let’s review what date and time functions look like in MS SQL Server.
Let’s start with functions that extract a year, month and day from a given date.
declare @dt date = '2014-10-20' select year (@dt) as year, month (@dt) as month, day (@dt) as day
SQL Server uses
SYSDATETIME() to get a current date and time. They are nondeterministic functions: views and expressions that reference these columns can’t be indexed. Both functions accept no arguments and return the local current date and time. The difference is, when we use
GETDATE(), the precision is to the milliseconds and in the case of
SYSDATETIME(), the precision is to the nanoseconds.
SELECT GETDATE(), SYSDATETIME()
DATEPART (datepart,date) returns an integer that represents the specified datepart of the specified date. Datepart could be specified by a whole name or an abbreviated name, for example:
- day could be specified as day, dd, d
- month could be specified as month, mm, m
- year could be specified as year yy.
A whole list of units and abbreviations is available in the documentation.
SELECT DATEPART(yy, GETDATE() ) as year, DATEPART(mm, GETDATE() ) as month, DATEPART(dd, GETDATE() ) as day, DATEPART(ww, GETDATE() ) as week
DATEADD (datepart, number,date) returns a new datetime value by adding an interval specified by datepart (days (day, dd, d), years (year, yy, y), months (month, mm, m), minutes (minute, mi, n) etc.)
SELECT DATEADD ( dd,-DAY( GETDATE()-1 ), GETDATE() ) as FirstDayCurrMonth, DATEADD ( dd, -1, DATEADD(m, DATEDIFF(m, 0, getdate()) + 1, 0)) as LastDayCurrMonth DATEADD ( dd,-DAY( GETDATE() ), GETDATE() ) as LastDayPrevMonth,
DATEDIFF(datepart, startdate, enddate) returns the difference between two dates in units specified by datepart.
declare @dt1 date = '2014-08-23', @dt2 date = '2014-10-28' select datediff (dd, @dt1, @dt2);
Let’s look at another query that retrieves employees and their current age.
Select name, surname, birth_date, datediff(yy, birth_date, getdate()) as age from employee
Other Arithmetic Operations With Dates
Date + number
select getdate() + 1 as tomorrow select getdate() + (10/1440) as ten_minutes_from_now
Date – number
select getdate() - 1 as yesterday
For some more information about dates and times in SQL, visit: Date and Time Data types and functions (Transact-SQL)