Have you ever had to do weekly/monthly reporting based on SQL data retrieval?
It would be pretty neat if you could get the start date of the current week/month dynamically, no?
Well, there’s a very straightforward way if you’re using Vertica or PostgreSQL – just employ the date_trunc(‘period’, timestamp) function.
Other databases (e.g. SQL Server, MySQL) require some extra SQL conversions, but certainly not anything unfamiliar 😉
Remember how to:
That’s all you need to know in order to truncate a date in SQL Server and MySQL.
Assuming we’re dealing with Monday-to-Sunday week, the said calculations will be as follows: