Export (0) Print
Expand All

Comparing DATEPART and DATENAME

SQL Server 2005

Updated: 14 April 2006

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)

Release History

14 April 2006

New content:
  • Added examples that use date strings.
  • Added an example that uses a variable.

Community Additions

ADD
Show:
© 2014 Microsoft