Database Reference
In-Depth Information
Figure 5-14.
Using date and time functions
How It Works
You use a nonstandard version of a query, omitting the
FROM
clause, to display the current date and time
and individual parts of them. The first two columns in the
SELECT
list give the complete date and time.
current_timestamp 'standard datetime', getdate() 'Transact-SOL datetime',
The first line uses the
CURRENTTIMESTAMP
value function of standard SQL; the second uses the
GETDATE
function of T-SQL. They're equivalent in effect, both returning the complete current date and time.
The next two lines each provide the current year. The first uses the T-SQL
DATEPART
function; the
second uses the T-SQL
YEAR
function. Both take a
DATETIME
argument and return the integer year. The
DATEPART
function's first argument specifies what part of a
DATETIME
to extract. Note that T-SQL doesn't
provide a
date
specifier for extracting a complete date, and it doesn't have a separate
DATE
function.
datepart(year, getdate()) 'datepart year', year(getdate()) 'year function',
The final line gets the current hour. The T-SQL
DATEPART
function must be used here since no
HOUR
function is analogous to the
YEAR
function. Note that T-SQL doesn't provide a time specifier for
extracting a complete time, and it doesn't have a separate
TIME
function.
datepart(hour, getdate()) 'hour'
