How to return the first day of a month in SQL and Snowflake
- How-Tos FAQs
- December 17, 2018
There is no date function to calculate and return the first day of a month in SQL. We can use other available date functions for this.
We will show an easy way of doing this using datameer as well as using more “manual” methods for returning the first day of the month.
1. Simplifying Date Transformation & Modeling With Datameer
Datameer (On Snowflake) is the one SaaS data transformation tool that takes the coding out of SQL coding.
Our low-code graphical user interface has features like a formula builder, SQL autocomplete and function-helps for all date functions.
Datameer supports advanced date operations such as :
To find the first day, the date time is first converted to date only using TO_DATE function.
Then, the date is changed to the first day of the month by using the DATE_FROM_PARTS function.
This works by inputting 01’ in the ‘day’ part of the function. Therefore changing the date to the first day of the month, without writing any code.
Other methods include:
2.) Returning the first day of a month in SQL SERVER
DECLARE @date DATETIME = '2020-03-17' SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @date), 0) AS First_Day_of_Month SELECT @date - DAY(@date) + 1 AS FIRST_DAY_OF_DATE -- In SQL Server 2012 and above SELECT DATEADD(DAY, 1, EOMONTH(@date, -1)) SELECT DATEFROMPARTS(YEAR(@date),MONTH(@date),1) -- Output First_Day_of_Month --------------------- 2020-03-01
3.) Returning the first day of a month in MySQL
SET @date ='2020-03-17'; SELECT DATE_ADD(@date, INTERVAL - DAY(@date) + 1 DAY) AS First_Day_of_Month; SELECT DATE_FORMAT(@date, '%Y-%m-01') First_Day_of_Month; -- Output # First_Day_of_Month 2020-03-01
4.) SNOWFLAKE SQL
Get first and last day of previous month in Snowflake:
select last_day(current_date – interval ‘2 month’) + interval ‘1 day’ as first_day;
| FIRST_DAY |
| 2019-12-01 |
select last_day( current_date – interval ‘1 month’ ) as last_day;
| LAST_DAY |
| 2019-12-31 |
As you can tell after assessing the four options, feel free to decide for yourself which is most convenient. 😉
Want to know more?
Feel free to explore this service with a free 14-day trial today!