从一个月的第一个表中选择所有数据

Question

我正在使用Data Studio查询关系数据库中的一个大表。我正在尝试获取数据类型为DATE的某一列是本月第一列的所有数据。列中的日期范围从1990年到2023年，格式为YYYY-MM-DD。

我对SQL不是很精通，我找到的所有类似的答案对我来说似乎都没有任何意义

如有任何帮助，我们将不胜感激。

SELECT * FROM data_table
WHERE date_column ... is the first of the month;

Answer 1

我假设DB2是因为您已经标记了IBMDataStudio:

你能这样使用DAYOFMONTH()吗？：

SELECT * FROM data_table
WHERE DAYOFMONTH(date_column) = 1;

Answer 2

使用ORACLE SQL，我将使用to_char(...)函数：

SELECT * FROM data_table
WHERE to_char(date_column, 'DD') = 1

Answer 3

SQL Server语法，用于定位月份的第一个。我使用CTE来填充一个包含日期的表，然后使用实际查询来只提取月份第一的日期。

WITH fakeDates AS
(
SELECT CAST('1/1/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('1/2/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('1/3/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('1/4/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('1/5/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('1/6/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('1/7/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('1/8/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('1/9/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('1/10/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('1/11/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('1/12/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('1/13/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('1/14/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('1/15/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('1/16/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('1/17/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('2/01/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('2/03/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('1/13/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('3/01/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('3/15/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('1/16/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('4/01/1998' AS DATETIME) AS DateField UNION ALL
SELECT CAST('4/02/1998' AS DATETIME) AS DateField 
)
SELECT fd.DateField
FROM fakeDates fd
WHERE DAY(fd.DateField) = 1