Selecting all data from a table where it is the first of the month

Question

I am using Data Studio to query a large table in a relational database. I am attempting to get all of the data where a certain column with the data type of DATE is the first of the month. The dates in the columns range from about 1990 to 2023 and are in the YYYY-MM-DD format.

I am not very proficient with SQL and all answers I've found for anything similar don't seem to make any sense to me.

Any help would be appreciated.

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

please clarify what database you use – Sergey Aug 11 '22 at 14:16 — Sergey, Aug 11 '22 at 14:16
It is a relational database – scottyboombox Aug 11 '22 at 14:21 — scottyboombox, Aug 11 '22 at 14:21
OK.The let's go step-by-step. Is it DB2? Is it Informix? – Sergey Aug 11 '22 at 14:23 — Sergey, Aug 11 '22 at 14:23

score 0 · Answer 1 · answered Aug 11 '22 at 14:23

0

Using ORACLE SQL, I would employ the to_char(...) function:

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

answered Aug 11 '22 at 14:23

Oceansunfish

42
7

score 0 · Accepted Answer · answered Aug 11 '22 at 14:28

0

I'm assuming DB2 because you've tagged IBM Data Studio:

Can you use DAYOFMONTH() like this?:

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

answered Aug 11 '22 at 14:28

Stephen Wuebker

189
6

This got the job done! All of the prior solutions I found that implemented the DAYOFMONTH() function where needlessly complicated and confusing or didn't work. This is simple and makes sense. Thank you. – scottyboombox Aug 11 '22 at 14:35
What if there is not an entry as the first day of the month? E.g 7th and 10. – Victor Axelsson Aug 11 '22 at 14:44
@VictorAxelsson That is a good point, to capture all non-end of month dates I used a bunch of AND's and OR's as such: `SELECT * FROM data_table WHERE DAYOFMONTH(dt_col) = 30 AND MONTH(dt_col) != 4 AND ((DAYOFMONTH(dt_col) = 30 AND MONTH(dt_col) != 6)) AND ((DAYOFMONTH(dt_col) = 30 AND MONTH(dt_col) != 9)) AND ((DAYOFMONTH(dt_col) = 30 AND MONTH(dt_col) != 11)) OR ((DAYOFMONTH(dt_col) = 28 OR DAYOFMONTH(dt_col) = 29) AND MONTH(dt_col) != 2) OR (DAYOFMONTH(dt_col) >= 1 AND DAYOFMONTH(dt_col) < 28);` – scottyboombox Aug 11 '22 at 15:48

score 0 · Answer 3 · answered Aug 11 '22 at 14:33

SQL Server Syntax to locate the First of the Month. I use a CTE to populate a table with dates and then the actual query to pull only the dates where it is the First of the Month.

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

Selecting all data from a table where it is the first of the month

3 Answers3