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:
SELECT DATEPART(year, GETDATE()) AS "Current 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:
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 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)
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 of year
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:
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;
To get the record of each row in the table for date parts, you may also use the query without WHERE clause.