Basic Date and Time Functions in MS SQL Server

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 GETDATE() and 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)