The DATEPART function in MS SQL Server

For returning the date part for a date e.g. day, month, year, hour, minute, second, the day of year, use the SQL DATEPART function.

The DATEPART function takes two arguments, for example:

The above statement will extract the month number from the current date of the system.

The DATEPART SQL function returns an integer e.g. 4 for April, 25 for the day if the specified date is 25^th, etc.

The next section shows examples of using the DATEPART() function to get different parts of the date.

I will use GETDATE() function as well as the date from the table column.

An example of getting the year from the current date

The GETDATE function returns the current date from the system where SQL Server is installed.

I will use this as the date in the DATEPART function. We will get the year part from the current date by using this query:

Query:

SELECT DATEPART(year, GETDATE())

AS "Current Year";

Use the day value in the first argument of the DATEPART to get the day number from the specified date. See the query and output for the current date:

The query:

SELECT DATEPART(day, GETDATE())

AS "The Current Day Number";

Assume that the current date is 25^th April 2018 then the output of the above query = 25.

The query for getting the current month

As such, the DATEPART returns an integer value. So, it returns the month number rather than the name for the specified date.

See a query and output for the month part:

SELECT DATEPART(month, '25 April 2017')

AS "Month Number";

The output:

An example of getting the hour from the current date

By using the hour value, you may get the hour from the specified date in the DATEPART function. See a query for that below:

SELECT DATEPART(hour, GETDATE())

AS "Current Hour";

List of date part values that can be used in DATEPART

We have already seen year, month, day, and hour usage in the above examples.

Following is the list of other values that you may use in the DATEPART function along with abbreviations:

DATEPART value	Abbreviation
quarter	qq, q
dayofyaer	dy, y
week	wk, ww
weekday	dw
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns
TZoffset
ISO_WEEL

Similarly, you may use the abbreviation values for year, month, day, and hour as follows:

• year (yy, yyyy)
• month (mm,m)
• day (dd, d)
• hour (hh)

Specifying a date column in a table example

In this example, I used a demo table, sto_employees that stores information like employee name, age, salary, and joining date of the employees.

In the query, I used the DATEPART function several times to get the:

• day
• month
• year
• hours
• day of year
• week
• milliseconds
• microseconds

For the date argument, I specified the join_date column of the example table while all values are returned in the same query as shown below:

The query:

SELECT emp_name, emp_age, join_date,

DATEPART(yyyy,join_date) AS "Joining Year",

DATEPART(mm,join_date) AS "Joining Month",

DATEPART(dd,join_date) AS "Joining Day",

DATEPART(hh,join_date) AS "Joining Hour",

DATEPART(dayofyear,join_date) AS "Day of Year",

DATEPART(week,join_date)  AS "Joining Week",

DATEPART(ms,join_date)  AS "Milliseconds",

DATEPART(mcs,join_date)  AS "microsecond"

FROM sto_employees

WHERE id = 6 OR id = 7;

The resultset:

To get the record of each row in the table for date parts, you may also use the query without the WHERE clause.