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

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;

CodePudding user response：

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;

CodePudding user response：

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

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

CodePudding user response：

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