SQL에서 달의 첫날을 얻는 방법은 무엇입니까?

For PostgreSQL, BigQuery, and Snowflake, use DATE_TRUNC('month', date) to get the first day of the month. 2. In SQL Server, use DATEFROMPARTS(YEAR(date), MONTH(date), 1) or DATEADD and DATEDIFF with a base date. 3. In MySQL, use DATE_FORMAT(date, '%Y-%m-01') or DATE_SUB(date, INTERVAL DAY(date)-1 DAY) for a date result. 4. In Oracle, use TRUNC(date, 'MONTH') to truncate to the first day. 5. In SQLite, use date('now', 'start of month'). Each method depends on the SQL dialect and returns the first day of the current month as a date value.

To get the first day of the month in SQL, the method varies slightly depending on the database system you're using. However, the general idea is to truncate or reset the day part of a date to the 1st while keeping the year and month.

Here are common approaches for popular SQL databases:

1. Using DATE_TRUNC (PostgreSQL, BigQuery, Snowflake)

SELECT DATE_TRUNC('month', CURRENT_DATE) AS first_day;

• DATE_TRUNC('month', date) sets the day to the 1st and zeros out time components.
• Works in PostgreSQL, BigQuery, Snowflake, and some others.

2. Using DATEFROMPARTS (SQL Server)

SELECT DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AS first_day;

• Extracts the year and month from the current date and builds a new date with day = 1.
• Very readable and reliable.

Alternatively, use:

SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS DATE) AS first_day;

• This calculates the number of months since a base date (0 = '1900-01-01'), then adds that many months to the base, effectively snapping to the first day.

3. Using STR_TO_DATE and DATE_FORMAT (MySQL)

SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01') AS first_day;

Or:

SELECT STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-', MONTH(CURDATE()), '-01'), '%Y-%m-%d') AS first_day;

• DATE_FORMAT(date, '%Y-%m-01') is simple and effective.
• Returns a string; wrap with CAST(... AS DATE) if you need a date type.

Better (returns DATE type):

SELECT DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE()) - 1 DAY) AS first_day;

• Subtracts (day - 1) days from the current date, landing on the 1st.

4. Oracle (using TRUNC)

SELECT TRUNC(SYSDATE, 'MONTH') AS first_day FROM dual;

• TRUNC(date, 'MONTH') truncates to the first day of the month.
• Very clean and efficient in Oracle.

Summary of Methods by Database

For SQLite:

SELECT date('now', 'start of month') AS first_day;

So, pick the method that matches your SQL dialect. Most of these can also be adjusted to work with a column instead of CURRENT_DATE or GETDATE().

Basically, just know your database’s date functions — and it’s usually straightforward.

위 내용은 SQL에서 달의 첫날을 얻는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!