SQL has a pretty extensive list of date time functions that come in handy when writing queries. I am going to cover a handful of the most common ones today.
GETDATE()
Getdate() returns the current datetime
select getdate()
Date Parts
use AdventureWorks2012 go SELECT * FROM [AdventureWorks2012].[Sales].[ShoppingCartItem]
First, looking at the data above. The data columns are in a format known as datetime in SQL (you may also hear many refer to it as a time stamp). It contains a date and time. What if you only want the date?
Convert()
SELECT convert(date, ModifiedDate) FROM [AdventureWorks2012].[Sales].[ShoppingCartItem]
To select the time only
SELECT convert(time, ModifiedDate) FROM [AdventureWorks2012].[Sales].[ShoppingCartItem]
YEAR(), MONTH(), DAY
SELECT year(ModifiedDate) as Year, month(modifieddate) as Month, day(modifieddate) as day from sales.SalesOrderDetail
You can filter by date elements too.
SELECT year(ModifiedDate) as Year, month(modifieddate) as Month, day(modifieddate) as day from sales.SalesOrderDetail where day(modifieddate) like '28'
Datepart()
You can extract time elements using Datepart()
SELECT datepart(hour, modifieddate) as hour, datepart(Minute, ModifiedDate) as minute FROM [AdventureWorks2012].[Sales].[ShoppingCartItem]
DateAdd()
dateadd() lets you add or subtract days or months or years to a date. In the example below I add 10 days. The syntax is dateadd(interval(d, m, y), amount, date)
select dateadd(d,10,getdate())
DateDiff()
Datediff() gives you the difference between two dates. Look at the dates in the query below:
We want to know how many days difference between DueDate and ModifiedDate
SELECT datediff(d,duedate, ModifiedDate) FROM [AdventureWorks2012].[Purchasing].[PurchaseOrderDetail]