Database Reference
In-Depth Information
How It Works
The
COUNT
function has different behavior depending upon the parameter passed to the function. If you
try
COUNT(*)
, the query will return you the number of total records available in the table, as shown in the
topmost results: table Person.Contact contains a total of 19,972 records, and
Count(*)
counts null.
If you pass a column name to the
COUNT
function, it will return the total number of records again, but
it will ignore all those rows that contain null values for that column. In the second query, you are
querying the same table, which has listed 19,972 records, but because your second query applies to the
Title
column, it returns only 1,009 records, because this time it has ignored all null values. In other
words,
Count(ColumnName)
ignores null.
DATETIME Functions
Although the SQL standard defines a
DATETIME
data type and its components,
YEAR
,
MONTH
,
DAY
,
HOUR
,
MINUTE
, and
SECOND
, it doesn't dictate how a DBMS makes this data available. Each DBMS offers
functions that extract parts of
DATETIME
s. Let's look at some examples of T-SQL
DATETIME
functions.
Try It: Using T-SQL Date and Time Functions
Let's practice with T-SQL date and time functions.
Open a New Query window in SQL Server Management Studio Express (database context does not
affect this query). Enter the following query, and click Execute. You should see the results shown in
Figure 5-14.
select
current_timestamp'standard datetime',
getdate()'Transact-SQL datetime',
datepart(year, getdate())'datepart year',
year(getdate())'year function',
datepart(hour, getdate())'hour'
