Retrieving parts of a date/time value with DATEPART

Like the YEAR, MONTH and DAY functions, the DATEPART function returns an integer which represents a specific part of the date/time value. For example, the following SELECT statement returns the same results as the preceding example:

SELECT DATEPART(yy, PostTime) AS [Year],
DATEPART(mm, PostTime) AS [Month],
DATEPART(dd, PostTime) AS [Day]
FROM DatabaseLog
WHERE DatabaseLogID = 1

The first thing to note is that, when you call DATEPART, you specify two arguments. The first argument determines the date/time component to retrieve, and the second argument is the source column. For the first argument, you must use one of the supported abbreviations to specify the datetime part. The following table lists the date/time parts you can retrieve and the abbreviations you must use to retrieve them:

For some datetime parts, more than one abbreviation is supported. You can use yy or yyyy as your first DATEPART argument to retrieve the year from the date/time value. Notice that the table includes abbreviations for date/time parts other than year, month or day. You can retrieve the quarter, the day of the year, the week of the year and the weekday, as shown in the following SELECT statement:

SELECT DATEPART(qq, PostTime) AS [Quarter],
DATEPART(dy, PostTime) AS [DayOfYear],
DATEPART(wk, PostTime) AS [Week],
DATEPART(dw, PostTime) AS [Weekday]
FROM DatabaseLog
WHERE DatabaseLogID = 1

As in the preceding example, each instance of DATEPART includes two arguments: the date/time part abbreviation and the source column. The statement returns the following results:

Notice that the weekday is shown as 6. By default, SQL Server begins the week with Sunday, so weekday 6 is equivalent to Friday.

The preceding two examples retrieved only values related to dates. However, as the table below shows, you can also retrieve data related to time:

SELECT DATEPART(hh, PostTime) AS [Hour],
DATEPART(mi, PostTime) AS [Minute],
DATEPART(ss, PostTime) AS [Second],
DATEPART(ms, PostTime) AS [Millisecond]
FROM DatabaseLog
WHERE DatabaseLogID = 1

This statement is retrieving the hour, minute, second and millisecond, as shown in the following results:


software quote best quote

Contact Us Now!
We'll Give You Our Best Quote For Your Commercial Software Needs!

Contact Us