Skip to content

Instantly share code, notes, and snippets.

@ateneva

ateneva/SQL_find_the_last_day_of_a_month.sql

Last active December 31, 2017 17:07
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ateneva/0a1a2fe8418abef04a7f11f86f63a9c3 to your computer and use it in GitHub Desktop.
Save ateneva/0a1a2fe8418abef04a7f11f86f63a9c3 to your computer and use it in GitHub Desktop.
Download ZIP
How to find the last day of a month
Raw
SQL_find_the_last_day_of_a_month.sql
---------------------------------SQL Server-------------------------------------------------------------------------
select
convert(date,getdate()) as Today,
eomonth(getdate(),-1) as LastDayPreviousMonth,
eomonth(getdate(),+0) as LastDayThisMonth,
eomonth(getdate(),+1) as LastDayNextMonth
Today |LastDayPreviousMonth |LastDayThisMonth |LastDayNextMonth |
-----------|---------------------|-----------------|-----------------|
2017-11-18 |2017-10-31 |2017-11-30 |2017-12-31 |
---------------------------------Vertica----------------------------------------------------------------------------
select
now() as Today
last_day(Now()) as LastDayofCurrentMonth,
last_day(add_months(Now(),-1)) as LastDayofPreviousMonth,
last_day(add_months(Now(), 1)) as LastDayofNextMonth
Today |LastDayPreviousMonth |LastDayThisMonth |LastDayNextMonth |
-----------|---------------------|-----------------|-----------------|
2017-11-18 |2017-10-31 |2017-11-30 |2017-12-31 |
#--------------------------------MySQL-------------------------------------------------------------------------------
select
date(now()) as Today,
last_day(adddate(Now(), -31)) as LastDayPreviousMonth,
last_day(Now()) as LastDayThisMonth,
last_day(adddate(Now(), +31)) as LastDayNextMonth
Today |LastDayPreviousMonth |LastDayThisMonth |LastDayNextMonth |
-----------|---------------------|-----------------|-----------------|
2017-11-18 |2017-10-31 |2017-11-30 |2017-12-31 |
---------------------------------PostgreSQL----------------------------------------------------------------------------
select
Date(Now()) as Today,
Date(date_trunc('month', Now() + interval '0 month') - interval '1 day') as LastDayPreviousMonth,
Date(date_trunc('month', Now() + interval '1 month') - interval '1 day') as LastDayThisMonth,
Date(date_trunc('month', Now() + interval '2 month') - interval '1 day') as LastDayNextMonth
today |lastdaypreviousmonth |lastdaythismonth |lastdaynextmonth |
-----------|---------------------|-----------------|-----------------|
2017-11-18 |2017-10-31 |2017-11-30 |2017-12-31 |
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment