How to return the first day of a month in SQL and Snowflake

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 :

• ADD_MONTHS
• DATE_FROM_PARTS
• DAY
• DAYOFWEEK
• DAYOFMONTH
• DAYOFYEAR
• HOUR
• MINUTE
• MONTH
• MONTHNAME
• MONTHS_BETWEEN
• NEXT_DAY
• PREVIOUS_DAY
• QUARTER
• SECOND
• TIME_FROM_PARTS
• TO_DATE

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!

Up Next:

Read How to insert a line break in SQL