CODEWITHSUNDEEP
sqlsql-server
zesla
zesla

Reputation: 11793

how to select date range by specified year-month in sql

I have a table in mysql server database called mytbl. There is a date column prod_date that contains all the date in year 2019 in the format 2019-xx-xx.

I need to extract all rows that from Jan 2019 to May 2019. My following code is not returning anything. 

SELECT * 
FROM mytbl
WHERE prod_date BETWEEN CONCAT('2019', '-', '01') AND CONCAT('2019', '-', '05')

Can someone tell what I did wrong? How I can select the date from by year and month only?

Thanks a lot in advance.

Upvotes: 2

Views: 3364

Answers (6)

Sumit Tiwary
Sumit Tiwary

Reputation: 29

Pre-assuming you are using SQL SERVER you can use : SELECT * FROM mytbl WHERE datepart(year,prod_date)='2019' and datepart(month,prod_date) in (01,02,03,04,05)

Upvotes: 0

Ishan
Ishan

Reputation: 295

Not a good solution but an edit for your query.

SELECT * FROM mytbl WHERE prod_date BETWEEN CONCAT('2019','-','01','-','01') AND CONCAT('2019','-','06','-','01')

Upvotes: 0

Bikram Limbu
Bikram Limbu

Reputation: 441

This should just give you the result.

SELECT * from mytbl WHERE Year(prod_date) = 2019 and Month(prod_date) BETWEEN 1 and 5

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

Just use an inequality:

SELECT * 
FROM mytbl
WHERE prod_date >= '2019-01-01' AND prod_date < '2019-06-01';

For a small explanation, the right condition restricts to dates strictly less than midnight of June 1, 2019, which includes all of May of the same year. Note that writing the WHERE clause this was is sargable, meaning that an index using the prod_date might be usable.

Upvotes: 0

Santosh Aryal
Santosh Aryal

Reputation: 1376

You can select the rows between two dates like if your date format is 'YYYY-MM-DD'. If you have the other formats, you can change the formats and query.

SELECT `mytbl`.* FROM `mytbl` where `prod_date` BETWEEN '2019-01-01' AND '2019-05-31'

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You don't need to use concat() just do :

where prod_date >= '2019-01-01' and prod_date <= '2019-05-31'

This assumes prod_date has YYYY-MM-DD format.

Upvotes: 0

Related Questions