Microsoft® SQL Server® 2012 Bible

194

Part II: Building Databases and Working with Data

FROM Sales.SalesOrderHeader

Result:

OrderDate Year Month Day OrderDay

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

2005-07-01 00:00:00.000 2005 July 1 Friday

There are two supported types: DateTime and DateTime2 were introduced in 2008. DateTime2 represents

time to a much fi ner granularity, within 100 nanoseconds.

The DatePart(date portion, date)returns the ordinal number of the selected portion

of the datetime value. The following example retrieves the day of the year and the day of

the week as integers:

SELECT DATEPART(dayofyear, CURRENT_TIMESTAMP) AS DayCount;

Result:

DayCount

-----------

122

SELECT DATEPART(weekday, CURRENT_TIMESTAMP) AS DayWeek;

Result:

DayWeek

-----------

3

An easy way to obtain just the date, stripping off the time, is to use a couple of string

functions:

SELECT CONVERT(char(10), CURRENT_TIMESTAMP, 112) AS "DateTime";

■ DateAdd(DATE PORTION, AMOUNT, BEGINNING DATE) and

DateDiff(DATE PORTION, BEGINNING DATE, ENDING DATE): Performs

addition and subtraction on datetime data, which databases often need to do.

The DATEDIFF() and the DATEADD() functions are designed expressly for this

purpose. The DATEDIFF() doesn’t look at the complete date, only the date part

being extracted:

select DATEDIFF(year,'september 4 2008','november 10 2009')

Result:

1

select DATEDIFF(month,'september 4 2008','november 10 2009')

2

The following query calculates the number of years and days that my wife, Karlyn, and I

have been married:

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

http://www.it-ebooks.info