Introduces date functions, and shows how to use them within where clauses
- [Narrator] Now for dates. Dates can be really tricky to get right in SQL and in most other programming languages. Problems arise, generally, because of the wide variety of date formats. The information I'm going to share with you, in this video, is taken from the SQL standard which is a set of rules governing how the language should work across all vendors. In many cases, the standard is ignored, though, and you should also know that database wide settings controlled by your IT Department might mean that dates appear differently in your database.
But if you're writing a query and supplying a date, this is the best place to start. So, the query that we're looking at here looks as though it ought to work. We're saying WHERE the last_update field is equal to the 15th of February, 2006, and if we look in this data in this field, we see they are uniformly set to the 15th of February. But when we run that query, we get a zero result set. And the reason for that, is you may have noticed the time there was set to half past four in the morning.
Now, because we haven't supplied a time in our query, the database software has assumed that we mean midnight entering into the 15th of the second, and, therefore, it doesn't find a match. If we used greater than, instead of equals, and ran that, we see that returns all of them because half past four in the morning is, indeed, later than midnight on the same day. Now according to the SQL standard, dates should be formatted in the following way.
We have the year, then the month, and then the day, and the three parts are separated by hyphens, not by slashes, not by dots. Now, as I said before, your database may present dates to you in a different format, but when it comes to specifying them, this is generally a good place to start. Separate the parts of your date with hyphens, and, we just changed that back to equals, separate parts of your time with colons, like so, and if we run that, we see that we've got almost all of the rows returned, 199.
One of them hasn't been returned because I've updated the row, myself, and so this number has changed. Now, one other thing that you might see here is a dot followed by a series of numbers, and what that means is it's part of a second. Most versions of SQL support this now, so this should be read as the 15th of February, 2006, four hours, 34 minutes, 33.35 seconds. That's how that bit should be read. And, in some versions of SQL, you can also store the time zone which is generally shown by a plus sign and then the number of hours using colon notation that are different.
So, that's how to make sense of dates, or attempt to make sense of dates the first time that you come across them, and generally how to specify them in a WHERE clause. So as we've just seen, we can provide an exact match for times and dates, if we wish, but there is an easier way. Just as LEFT and UPPER are string functions, so we've got some date functions to help this sort of thing easier. The first one we're going to look at is YEAR, and we're going to say WHERE YEAR of last_update is 2006, and by running the YEAR function on the dates, see that we are returning a numeric result there.
And, you see, that's returned us, again, 199 rows, all but one of our rows. YEAR works in MySQL and in Microsoft. In Oracle or PostgreSQL, you would use extract, or you might use DATE_PART. In MySQL, along with YEAR, you can use MONTH, WEEK, DAY, HOUR, MINUTE and SECOND in a similar way. In many versions, you can extract data down to the nano-second. You can also do useful things like returning the day of the week, Monday to Sunday, or the day of the month, or the quarter, or the week number of the year.
The most likely requirement, though, is just to extract the date out of a field that contains both date and time. In MySQL and PostgreSQL, you can just use DATE, and then, over here, this would now become a date format, so we would use single quotes and say 02, 15, like so. With Microsoft 2008 and later, you would need to use the CONVERT function, and in Oracle you would use TO_DATE coupled with TO_CHAR.
Another key date function you should know about is date format, particularly if using a European-style DATE_FORMAT is going to cause problems or misunderstanding. You can use DATE_FORMAT to switch it to American. So, in MySQL we would say, SELECT DATE_FORMAT, then you'd give a field name, and then you sort of use a mini-language that supplied, or defined, by the vendor, and we could say, give me the month, then the day, and then I'm gonna take the full year, and that's capital-Y in MySQL, FROM actor, like so, and you can see that's returned it to us in an American-style date format.
You can use FORMAT to return all sorts of weird and wonderful things, actually. So, we could say, capital-D, for day, capital-M for month, and you can actually retrieve the names of the month and day values with a -th after them. The bad news is that, once again, different types of SQL use different key words and different formatting conventions. So, this %-notation is what you would use in MySQL, for example, but you'd need to look up which one's right for your version.
For Oracle and PostgreSQL, instead of DATE_FORMAT, you would use TO_CHAR. In Microsoft, as long as you're using SQL Server 2012 or later, you can use FORMAT, but you would need to supply the formatting information in a Microsoft friendly way.
Join Emma Saunders as she shows you how to design and write simple SQL queries for data reporting and analysis. Review the different types of SQL, and then learn how to filter, group, and sort data, using built-in SQL functions to format or calculate results. Learn a bit about data types and database design. Discover how to perform more complex queries, such as joining data together from different database tables. Last but not least, Emma shows how to save your queries as views, so you can run them again and again.
- Using different versions of SQL
- Retrieving data with SELECT statements
- Filtering and sorting your results
- Transforming results with built-in SQL functions
- Grouping SQL results
- Merging data from multiple tables
- Identifying data types, and how to make sense of your database design
- Saving SQL queries