Apart from the standard datediff() and timestampdiff(), Vertica and MySQL offer a couple of more ways to go about it: By using the age functions: age_in_years (current date, start date) and age_in_months (current date, start date) A key thing to remember about these two is that, unlike datediff ('years', start date, current date), they will calculate the months/years between the … Continue reading How do I find the difference between two time periods?

# Tag: dates

# How do I find the next Monday/Tuesday/etc.. with SQL?

Being able to calculate the date of the coming Thursday may come really handy if your sales revenue follows a very strong weekday pattern as it enables you to use weekdays, rather than fixed dates, as a basis for making your weekly financial projections. How do I do this in practice? Vertica offers a very … Continue reading How do I find the next Monday/Tuesday/etc.. with SQL?

# How do I find the last day of the previous/next month with SQL?

Need to provide a daily estimation of how many clicks you'll make by the end of next month? Your client expects you to deliver a monthly report on their performance? That's when knowing how to get the last day of the current, previous or next month might come in handy Whereas Vertica and MySQL offer … Continue reading How do I find the last day of the previous/next month with SQL?

# How do I find the first day of previous/next week with SQL?

I saw your article the other day on getting the first date of the current week.. But what if I wanted to get the first date of the previous week? Or the first date of next week? As you've already guessed, the SQL syntax for getting the first day of the current week will require … Continue reading How do I find the first day of previous/next week with SQL?

# How do I find the first day of the current week with SQL?

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) … Continue reading How do I find the first day of the current week with SQL?

# How do I calculate the remaining days until the end of a fiscal quarter?

Should you work for a public company, it is very likely that the fiscal year of your company may not be the same as the calendar year. This in turn, means, that a bunch of your analyses may need to forecast financials for the fiscal quarters, rather than the calendar ones. How could you then … Continue reading How do I calculate the remaining days until the end of a fiscal quarter?

# How do I calculate the remaining days until the end of a calendar quarter?

It may sound overwhelming but it's actually quite simple, especially if you've already figured out how to: extract various time periods from a date get the end date of the current quarter calculate the number of days between two days As you'll see below - calculating the remaining days in current quarter is then nothing … Continue reading How do I calculate the remaining days until the end of a calendar quarter?