Comparing DATEPART and DATENAME

The DATEPART and DATENAME functions produce the specified part of a datetime value such as the year, quarter, day, or hour, as either an integer or an ASCII string. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used with either of these functions, the seconds and milliseconds returned are always zero.

The following examples assume the date of May 29:

SELECT DATEPART(month, GETDATE());
GO

Here is the result set.

------------
5

(1 row(s) affected)

SELECT DATENAME(month, GETDATE());
GO

Here is the result set.

------------
May

(1 row(s) affected)

In the following examples, the first two statements use date strings instead of the GETDATE function. The third block of statements uses a datetime variable.

SELECT DATEPART(day, 'May 29, 2006');
SELECT DATEPART(day, '2006/05/29');

DECLARE @datevar datetime
SET @datevar = '20060529'
SELECT DATEPART(day,@datevar)

See Also

Other Resources

DATENAME (Transact-SQL)
DATEPART (Transact-SQL)
Date and Time Functions (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History