Get the first day of the month in date column in sql without providing date

Question

I have a price column and a date column in an sql database which contains daily prices for the last 10 years. I want to be able get the price for the 1st of the month for each month in the table without providing a date.

All solutions so far have required me to provide a date such as this: How can I select the first day of a month in SQL?

I am fairly new to SQL and so am unsure of the code within the answer. Any help and explanation would be greatly appreciated.

Thanks

@GordonLinoff The database is an internal SQL database so I'm am unsure what you mean. — gerardcslabs, Mar 30 '21 at 11:16
_internal SQL database_ - how do you connect to the database to run queries (what tools do you use?) — irnerd, Mar 30 '21 at 12:03

score 2 · Accepted Answer · answered Mar 30 '21 at 10:42

2

One method uses simple filtering:

select t.*
from t
where extract(day from date) = 1;

Date functions are particularly database-dependent. EXTRACT() is standard SQL, but many databases use DAY() to extract the day of the month from the date.

answered Mar 30 '21 at 10:42

Gordon Linoff

1,242,037
58
646
786

As a newbie, I'm unsure what the database-dependency is? Do you mean like structure? Also with the answer wouldn't I have to from the date? – gerardcslabs Mar 30 '21 at 11:16
@gerardcslabs . . . date/time functions depend on the database being used. – Gordon Linoff Mar 30 '21 at 12:16

Get the first day of the month in date column in sql without providing date

1 Answers1