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 etc., use the SQL DATEPART function.

The DATEPART function takes two arguments, for example:

SELECT DATEPART(month, GETDATE());

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 25th etc.

The next section shows examples of using the DATEPART() function for getting different parts of the date. I will use GETDATE() function as well as date from the table column.

An example of getting 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";

SQL DATEPART year

Use the day value in the first argument of the DATEPART for getting 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 25th 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 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:

SQL DATEPART month

The example of getting the hour from 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 above example. Following is the list of other values that you may use in the DATEPART function along with abbreviations:

  • 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 DATEPART function several times for getting 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:

SQL DATEPART table

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

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!