Microsoft® SQL Server® 2012 Bible

195

Chapter 8: Data Types, Expressions, and Scalar Functions

8

SELECT

DATEDIFF(yy,'6/28/1997', CURRENT_TIMESTAMP) AS MarriedYears,

DATEDIFF(dd,'6/28/1997', CURRENT_TIMESTAMP) AS MarriedDays;

Result:

MarriedYears MarriedDays

------------ -----------

15 5411

The next query adds 100 hours to the current millisecond:

SELECT DATEADD(hh,100, CURRENT_TIMESTAMP) AS [100HoursFromNow];

Result:

100HoursFromNow

------------------------

2009-11-21 18:42:03.507

The following query is based on the AdventureWorks sample database and calculates

the number of days between the order and ship dates for a customer’s order, using the

DateDiff() function:

USE ADVENTUREWORKS

GO

SELECT

p.FirstName +' '+p.LastName CustomerName,

DateDiff(dd,OrderDate, ShipDate ) DaysDiff

FROM Sales.SalesOrderHeader soh

INNER JOIN Sales.Customer c

on soh.CustomerID = c.CustomerID

INNER JOIN Person.Person p

ON c.PersonID = p.BusinessEntityID

Because the function is in a column expression, it is calculated for each row in the

result set:

CustomerName DaysDiff

------------- ----------

James Hendergart 7

Takiko Collins 7

Jauna Elson 7

...

The ToDateTimeOffset(expression, time_zone) returns a DateTimeOffset value.

The following example gets the date and time for a given time zone:

SELECT TODATETIMEOFFSET(CURRENT_TIMESTAMP,'-07:00');

Result:

2009-11-05 11:24:15.490 -07:00

c08.indd 195c08.indd 195 7/30/2012 4:21:15 PM7/30/2012 4:21:15 PM

http://www.it-ebooks.info