Working with dates is a fundamental aspect of database management and data analysis. SQL provides a rich set of date functions to handle everything from simple date arithmetic to complex date manipulation.
There are many ways to build SQL competency so we have decided to take a different approach.
For the next few days, you will be serving as the newly hired Data Analyst for Basira and Co, An Afro fusion restaurant with a network of chains across Africa and you’ll be reporting to the Chief Growth Officer (CGO) Mrs Ahmadu.
Everyday, you’ll get analytical requests/tasks from her related to the SQL topic of the day. The goal for this is to make each lesson as practical and as relatable as possible.
HOW TO PRACTICE: Here is a link to our practice guide. We have already prepared the table schema in advance so all you need to do is plug that into DBFiddle.com and you are all set! The LinkedIn forum is a great way to share any comments or questions about the practice guide.
YOU’VE GOT MAIL!
Good Morning, I have the manager for our new york branch flying in this weekend. I’d like to go over the dishes ordered since inception. Can you provide this list and please format the dates so it’s a lot easier to read.
Thanks!
Mrs Ahmadu (CGO)
SQL TOPIC OF THE DAY
1. EXTRACT() Function
| SYNTAX SELECT EXTRACT(part FROM date); |
Use Cases and Examples:
– Extract Year:
| SELECT EXTRACT(YEAR FROM order_date) AS order_year FROM orders; |
Extracting the year from order dates helps in grouping and analyzing orders on a yearly basis. This is useful for generating annual reports, understanding year-over-year growth, and making strategic decisions based on yearly trends.
– Extract Month:
| SELECT EXTRACT(MONTH FROM sale_date) as sale_month, SUM(amount) as monthly_sales FROM sales GROUP BY sale_month; |
By extracting the month and summing the sales amounts, this query helps in analyzing sales data on a monthly basis. Businesses can use this information to identify seasonal trends, plan inventory, and adjust marketing strategies to maximize sales during peak months.
2. DATE_FORMAT() Function
| SYNTAX SELECT DATE_FORMAT(date, format); |
Use Cases and Examples:
– Custom Date Format:
| SELECT DATE_FORMAT(order_date, ‘%M %d, %Y’) as formatted_date FROM orders; |
This query formats order dates to a more readable style, such as “January 01, 2024”. Custom date formats are useful for generating user-friendly reports, invoices, and receipts, enhancing the readability and presentation of dates.
– User-Friendly Display:
| SELECT user_id, DATE_FORMAT(birth_date, ‘%W, %M %e, %Y’) as birth_date FROM users; |
Displaying birth dates in a user-friendly format, such as “Monday, January 1, 2024”, improves the user experience in applications by presenting dates in a more understandable and familiar way. This can enhance the overall usability and appeal of the application.
SQL PRACTICE
Now, let’s write a script to answer the question of the day.
Mrs Ahmadu would like you to provide a list of the dishes ordered since inception in the new york branch with formatted dates.
| SELECT order_id, dish_name, DATE_FORMAT(order_date, ‘%M %d, %Y’) FROM orders WHERE restaurant_id = ‘gk7e’ |
FEEDBACK FROM THE CGO
This looks quite nice! Can you also add the days of week for each order in the new date column?
Warm regards,
Mrs Ahmadu (CGO)
With what we learned today, think you can solve this part on your own?
Did you learn anything new? Share your new insights with us on Facebook, LinkedIn or Instagram and don’t forget to tag us!
