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) 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:

---------------Vertica------------------------------------------------------------------------
select
Date(date_trunc('week', Now())) as FirstDayOfCurrentWeek,
Date(date_trunc('month', Now())) as FirstDayofCurrentMonth,
Date(date_trunc('year', Now())) as FirstDayOfCurrentYear
firstdayofcurrentweek |firstdayofcurrentmonth |firstdayofcurrentyear |
----------------------|-----------------------|----------------------|
2017-11-06 |2017-11-01 |2017-01-01 |
---------------PostgreSQL----------------------------------------------------------------------
select
Date(date_trunc('week', Now())) as FirstDayOfCurrentWeek,
Date(date_trunc('month', Now())) as FirstDayofCurrentMonth,
Date(date_trunc('year', Now())) as FirstDayOfCurrentYear
firstdayofcurrentweek |firstdayofcurrentmonth |firstdayofcurrentyear |
----------------------|-----------------------|----------------------|
2017-11-06 |2017-11-01 |2017-01-01 |
----------------------------SQL Server---------------------------------------------------------
select
convert(date, getdate()) as Today,
datepart(dw, getdate()) as Weekday,
case ----------------------------Mon-Sun week-----------------------------------------
when datepart(dw, getdate()) = 1 then convert(date, dateadd(day, -6, getdate()))
when datepart(dw, getdate()) = 2 then convert(date, dateadd(day, 0, getdate()))
when datepart(dw, getdate()) = 3 then convert(date, dateadd(day, -1, getdate()))
when datepart(dw, getdate()) = 4 then convert(date, dateadd(day, -2, getdate()))
when datepart(dw, getdate()) = 5 then convert(date, dateadd(day, -3, getdate()))
when datepart(dw, getdate()) = 6 then convert(date, dateadd(day, -4, getdate()))
when datepart(dw, getdate()) = 7 then convert(date, dateadd(day, -5, getdate()))
end as FirstDayofCurrentWeek,
convert(date, convert(varchar(7), getdate(), 120) + '-01') as FirstDayofCurrentMonth,
convert(date, convert(varchar(4), getdate(), 120) + '-01-01') as FirstDayofCurrentYear
Today |Weekday |FirstDayofCurrentWeek |FirstDayofCurrentMonth |FirstDayofCurrentYear |
-----------|--------|----------------------|-----------------------|----------------------|
2017-11-12 |1 |2017-11-06 |2017-11-01 |2017-01-01 |
-------------------------------MySQL--------------------------------------------------------------
select
Date(Now()) as Today,
weekday(Now()) as WeekdayToday,
case #---------------------------Mon-Sun week--------------------------------
when weekday(Now()) = 0 then date(Now())
when weekday(Now()) = 1 then date(date_add(Now(), interval -1 day))
when weekday(Now()) = 2 then date(date_add(Now(), interval -2 day))
when weekday(Now()) = 3 then date(date_add(Now(), interval -3 day))
when weekday(Now()) = 4 then date(date_add(Now(), interval -4 day))
when weekday(Now()) = 5 then date(date_add(Now(), interval -5 day))
when weekday(Now()) = 6 then date(date_add(Now(), interval -6 day))
end as FirstDayofCurrentWeek,
date_format(Now(), '%Y-%m-01') as FirstDayofCurrentMonth,
date_format(Now(), '%Y-01-01') as FirstDayofCurrentYear
Today |WeekdayToday |FirstDayofCurrentWeek |FirstDayofCurrentMonth |FirstDayofCurrentYear |
-----------|-------------|----------------------|-----------------------|----------------------|
2017-11-12 |6 |2017-11-06 |2017-11-01 |2017-01-01 |

Happy SQL-ing!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.