SQL Query to filter by Year in Excel as database

Hi @Abhi25 ,

You can try with LIKE command within the SELECT query for this requirement. 

For e.g. SELECT * from [sheet1$$] WHERE $Date$ LIKE %2021%

Date is a variable here to which the data must be saved from the Date column of your sheet.

Thanks for the reply, in my previous experiences I have been using the column name directly in queries like the one you have mentioned.

Can i not use the column name in this case also?

Yes, you can use.  Then, the query will change as below: 

SELECT * from [sheet1$$] WHERE [Date] LIKE %2021%

Hi @Abhi25 ,

I can see that @Padmakumar  has answered your question.
If you find it satisfactory then we’d appreciate it if you could mark his answer as the solution as it increase engagement here on the community.
.
.
.
Also if you’d like to explore more on database operations using Excel and CSV(omg csv?!) then I’ve written an article that you may find useful.

Remember, the excel database operations follow MS Access conventions. I usually go here anytime I get stuck.

Kind Regards,
Ashwin A.K

I tried using the above query & it ran for sometime and ended without giving any errors but the result was inconsistent. The no of rows obtained from the query were far less than the actual number of rows that should have been there

Thanks for the reply Ashwin, as I have already mentioned in my reply above that it is giving me inconsistent results. Since there are more then 6 lakh records in an excel that I am dealing with and I want to get data from the required that's having year as 2022 .

Does the received output contain all 2021 data?

Hi @Abhi25 ,

I gave it a try from my end, with test data that I’ve prepared and this seems to work well:

SELECT * FROM [Sheet1$$]
WHERE YEAR([Date]) = 2022

I’m not sure if this is exactly what you were looking for, but if you wish to drill down deeper then here are some MS docs that can help you with that.

Kind Regards,
Ashwin A.K

How can I fetch data between two date ranges using select query?

For eg I want the records that lie in the month of Oct 2022-Dec 2022. I am using below select query :

select * from [Sheet1$$] where [Date] BETWEEN #10/1/2022# AND #12/31/2022# 

the query is giving me records of every year for the months of Oct-Dec instead I want the records for just 3 months (Oct,Nov,Dec) of the year 2022.

P.S. - I am using excel as database.