SQL Get current month

Question

I have table1 with data

file  Startdate
A     2020-05-01
B     2020-06-01

We have today is 2020-06-01 and I could use Select and where startdate ='2020-06-01'
But I try this query and not get 2020-06-01

select file from table1
where
 startdate >= dateadd(month, -1, datefromparts(year(getdate()), month(getdate()), 1))
         and startdate < datefromparts(year(getdate()), month(getdate()), 1)

Please correct me. Thank you

Are you just looking for `SELECT [File] FROM Table1 WHERE StartDate = CONVERT(DATE, GETDATE());`? — Ilyes, Jun 01 '20 at 19:29
Does this answer your question? [WHERE Clause to find all records in a specific month](https://stackoverflow.com/questions/851236/where-clause-to-find-all-records-in-a-specific-month) — Luis Cardoza Bird, Jun 01 '20 at 19:33
Great. If I added a record with startdate = '2020-06-02' then the query SELECT [File] FROM Table1 WHERE StartDate = CONVERT(DATE, GETDATE()) will pick 2 records for the month July? — q phan, Jun 01 '20 at 19:34
Your current filter says "give me all the data for last month." If you want to include data from this month, you need to write a query that doesn't exclude data from this month. How do you expect `< '2020-06-01'` to return the value `'2020-06-01'`? — Aaron Bertrand, Jun 01 '20 at 21:40

Ilyes · Accepted Answer · 2020-06-01T20:06:44.583

2

You're looking for

select [file] from table1
where
 startdate = datefromparts(year(getdate()), month(getdate()), 1)

This'll returns

file  Startdate
B     2020-06-01

UPDATE:

I mean not just 2020-06-01 but if I add 2020-06-02 then should get 2 records for 2020-06-01 and 2020-06-02

SELECT [File]
FROM Table1
WHERE StartDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
      AND
      StartDate <= EOMONTH(GETDATE());

edited Jun 01 '20 at 20:06

answered Jun 01 '20 at 19:33

Ilyes

14,640
4
29
55

so this query will pick any day of july month? – q phan Jun 01 '20 at 19:41
When we 're on July, it 'll pick only `2020-07-01`. Since we're not yet on July it 'll pick only `2020-06-01` – Ilyes Jun 01 '20 at 19:42
I mean not just 2020-06-01 but if I add 2020-06-02 then should get 2 records for 2020-06-01 and 2020-06-02 – q phan Jun 01 '20 at 19:46
1

Aside: [`EoMonth`](https://learn.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql?view=sql-server-ver15) returns a `Date`. If it is compared to a `DateTime` then any time past midnight at the start of the last day of the month tends to get ignored. A solution is to use `... and StartDate < DateAdd( day, 1, EoMonth( GetDate() ) );`. Note `<` rather than `<=` to accept all times up to, but not including, midnight at the start of the next month. – HABO Jun 01 '20 at 20:31

Griffin · Answer 2 · 2020-06-01T21:22:28.250

-1

datefromparts clutter it, it's much easier to write and understand it like this. Mysql

SELECT DATE_FORMAT(getdate(), "%Y-%m-01");

MSSql

SELECT CONVERT(NOW(), varchar(8),120) + '01'

edited Jun 01 '20 at 21:22

answered Jun 01 '20 at 20:00

Griffin

785
5
13

2

`DATE_FORMAT()` is a MySQL function while the question tagged with SQL Server. – Ilyes Jun 01 '20 at 20:01
Fair enought, added a line for mssql as well – Griffin Jun 01 '20 at 21:24
@Griffin, I think you were looking for this... `SELECT cast(format(GETDATE(), 'yyyy-MM-01') as Date)` – SS_DBA Jun 01 '20 at 21:28
@SS_DBA well format is only supported from sql 2012 and forward, convert has been around forever. But thanks for updating me that format has been introduced, it's most wellcome. Casting it as date might be a good idea for performance, but the engine will convert the text to date for you otherwise (not really sure if it has any impact in real world). – Griffin Jun 03 '20 at 07:45

score -1 · Answer 3 · answered Jun 01 '20 at 20:30

-1

select * from table1 
where StartDate= cast(getdate() as date)

answered Jun 01 '20 at 20:30

Esperento57

16,521
3
39
45

SQL Get current month

3 Answers3