1. Functions returning date and time parts
Let's move on to another interesting topic.
In this video, you will learn how to retrieve only the parts that you need from a date. If you only need to work with the year, month or day from a certain date, there are functions you can use.
2. YEAR(date)
Say you want to look at the people who started to rate chocolate in 2018.
A function that extracts the year from a given date can help you with the query.
SQL Server provides the YEAR() function
and this is an example how to use it.
The result is a new column, containing only the extracted year part from a date.
3. MONTH(date)
Besides the year, you may want to extract the month of a given date as a separate column.
You can do that using the MONTH() function.
It works in the same way as YEAR(), as visible in this example.
The results are integer numbers, so don't expect this function to give you the actual name of the month, like January, March and so on.
4. DAY(date)
Similar to the previous two,
the DAY() function returns the day part from a given date.
This query shows how to use it.
The result is an integer, and should not be confused with the week day.
5. DATENAME(datepart, date)
With DATENAME(), you return the name of a certain part of a date.
For the "datepart" parameter, you can use a noun or an abbreviation, like in this table.
These are some examples of date parts you can use but there are many more.
You can find the complete list of date parts in the MSDN documentation.
6. DATENAME() example
Let's practice an example.
I also included the previous functions, to compare the results.
DATENAME() used with the "year" part has the same output as the YEAR() function.
When extracting the month, the result is different than what the MONTH() function returns. In this case, you get the actual name of the month instead of only a number.
For the day, we get the same result from both functions, DAY() and DATENAME() used with the "day" parameter.
If we call DATENAME() with the "weekday" parameter, the output is the name of the day within the week (Monday, Tuesday, etc.)
7. DATEPART(datepart, date)
A similar function is DATEPART().
This works in the same way as DATENAME(), the difference being that the returned values are all integers.
You can see in this example how similar these two functions are.
If you need the actual name of a date part, you should use DATENAME(). If the number is sufficient, then DATEPART() is more appropriate.
8. DATEFROMPARTS(year, month, day)
There is another function, DATEFROMPARTS(), which is the opposite of the previous ones: you use it with 3 parameters: integer values representing the year, month and day (in this order) and the function generates a date.
If you execute this query, the result will be a date: 5th of March, 2019.
9. DATEFROMPARTS(year, month, day)
In this second example, I first extract the year, month and day date parts from a string representing a date.
Applying DATEFROMPARTS() with the parts we just extracted, will result in the initial date, with one difference: the data type returned by DATEFROMPARTS() is a date, while the initial value was a string of characters.
10. Let's practice!
These functions are quite fun and easy to use. Check them out on your own in the next exercises.