Comparing results with today's date?
⚡TLDR
In MySQL, use CURDATE() or in SQL Server, use CAST(GETDATE() AS DATE) to compare columns with today's date:
- MySQL:
-- Find all cowboys who rode today WHERE last_ride_date = CURDATE() - SQL Server:
-- Don't allow time-travelling, compare only today's events WHERE CAST(event_date AS DATE) = CAST(GETDATE() AS DATE)
Include the entire day up to now:
- MySQL:
-- Show gorillas who've been active from today's dawn WHERE last_seen_datetime >= CURDATE() AND last_seen_datetime < CURDATE() + INTERVAL 1 DAY - SQL Server:
-- Get customers who've entered matrix from this morning WHERE matrix_entry_datetime >= CAST(GETDATE() AS DATE) AND matrix_entry_datetime < DATEADD(DAY, 1, CAST(GETDATE() AS DATE))
PostgreSQL and Oracle: Comparing with today
In PostgreSQL, use current_date to represent today's date without time:
-- Find all potatoes baked today, because who doesn't love a hot spud? WHERE bake_date = current_date
In Oracle, utilize trunc() to remove the time component:
-- Show all cars sold today, "vroom-vroom" as Oracle truncates time just like Vin Diesel in Fast and Furious! WHERE trunc(sold_date_time) = trunc(sysdate)
Time intervals and Performance: Keeping your SQL zippy
For where precise time ranges matter:
- MySQL:
-- Dogs meeting between now and tomorrow. No cats allowed! WHERE dog_meeting_time BETWEEN NOW() AND NOW() + INTERVAL 1 DAY - SQL Server:
-- Humans teleporting between now and tomorrow. Aliens, hold up! WHERE teleport_time BETWEEN CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE))
Regarding indexes and performance:
- Avoid altering data in queries, it's like trying to change car parts while driving at 60mph. Pro-tip: indexes don't like it.
- When performance is a concern, use database-specific functions that are index-friendly. Because nobody likes waiting. Not even your queries.
SQLite and the Date Soup
SQLite doesn't have specific date types, but it doesn't stop us from extracting dates using strftime.
-- Grab weapons used today, in SQLite, every day is a battle! WHERE strftime('%Y-%m-%d', weapon_used_datetime) = strftime('%Y-%m-%d', 'now', 'localtime')
When it comes to extricating only a few parts of a date:
-- It's a match! If it's the same day, same month. It's like how socks should be. WHERE DAY(sock_found_date) = DAY(GETDATE()) AND MONTH(sock_found_date) = MONTH(GETDATE())
Every SQL database speaks a different dialect. Understanding these unique quirks is half the battle won. Remember to cast and convert judiciously.
Linked
Linked
Was this article helpful?