Handling Dates in SQL Server

Manipulating dates on any platform can be difficult. In SQL Server, there are several functions that make this task a little easier.

GetDate – Returns the current system date. [GetDate()]

GetUTCDate – Returns the current UTC date/time. [GetUTCDate()]

DatePart – Returns an integer that represents the specified part of the specified date. [DatePart(datepart,date)]

DateAdd – Returns a new datetime value based on adding an interval to the specified date. [DateAdd(datepart ,number,date)]

DateDiff – Returns the number of date and time boundaries crossed between two specified dates. [DateDiff(datepart,startdate,enddate)]

DateName – Returns a character string representing the specified datepart of the specified date. [DateName(datepart,date)]

–

Below are some sample uses of the above functions:

   1: SELECT

   2:     GETUTCDATE() AS UtcDate, 

   3:     GETDATE() AS CurrentDate,

   4:  

   5:     -- Equivelent to using MONTH(GETDATE()), DAY(GETDATE()) and YEAR(GETDATE())

   6:     DATEPART(month, GETDATE()) AS CurrentMonth,

   7:     DATEPART(day, GETDATE()) AS CurrentDay,

   8:     DATEPART(year, GETDATE()) AS CurrentYear,

   9:  

  10:     -- Add 3 months to the date

  11:     DATEADD(month ,3,GETDATE()) AS ThreeMonthsFromNow,

  12:  

  13:     -- Number of days between today and Christmas

  14:     DATEDIFF(day,GETDATE(),'12/25/2007 00:00:00') AS DaysTillChristmas,

  15:  

  16:     -- Return the character month

  17:     DATENAME(month,GETDATE()) AS MonthText

–

There are many more ways to manipulate dates. I have provided links to some related articles below. I especially like the FormatDate function I found in my SQL Server Magazine this month created by Roy Byrd.

Display Dates in the Format You Need
SQL Server DateTime Formatting
How to get the name of the day of date
Retrieving the Date From a SQL Server DateTime
Working with Date/Time values in SQL: Don’t Format/Convert — just use DATETIME

–