SQL Date And Time


Example

In SQL, you use date and time data types to store calendar information. These data types include the time, date, smalldatetime, datetime, datetime2, and datetimeoffset. Each data type has a specific format.


Data typeFormat
timehh:mm:ss[.nnnnnnn]
dateYYYY-MM-DD
smalldatetimeYYYY-MM-DD hh:mm:ss
datetimeYYYY-MM-DD hh:mm:ss[.nnn]
datetime2YYYY-MM-DD hh:mm:ss[.nnnnnnn]
datetimeoffsetYYYY-MM-DD hh:mm:ss[.nnnnnnn] [+/-]hh:mm

The DATENAME function returns the name or value of a specific part of the date.

SELECT DATENAME (weekday,'2017-01-14') as Datename
Datename
Saturday

You use the GETDATE function to determine the current date and time of the computer running the current SQL instance. This function doesn't include the time zone difference.

SELECT GETDATE() as Systemdate 
Systemdate
2017-01-14 11:11:47.7230728

The DATEDIFF function returns the difference between two dates.

In the syntax, datepart is the parameter that specifies which part of the date you want to use to calculate difference. The datepart can be year, month, week, day, hour, minute, second, or millisecond. You then specify the start date in the startdate parameter and the end date in the enddate parameter for which you want to find the difference.

SELECT SalesOrderID, DATEDIFF(day, OrderDate, ShipDate) 
AS 'Processing time'
FROM Sales.SalesOrderHeader
SalesOrderIDProcessing time
436597
436607
436617
436627

The DATEADD function enables you to add an interval to part of a specific date.

SELECT DATEADD (day, 20, '2017-01-14') AS Added20MoreDays
Added20MoreDays
2017-02-03 00:00:00.000