1

I have a date= 2016-12-25 at hand, and i want to find all the records of this particular date. The records in the database are stored as a range of startDate and endDate. Example

|_____Name___|___Class_____|___StartDate_____|___EndDate______|

|_____Maths___|____Lecture___|___2016-12-10____|___2016-12-30____|

so now using the sql query i want to display this class with the user selected date 2016-12-25 where I am unaware os StartDate and EndDate (All i have in hand in the user specified date, the StartDate and EndDate could differ for each subjects). Can anybody please help me out?

will it be something like this? query: select all records where 2016-12-25 between all StartDate and endDate

Ramsha Khan
  • 99
  • 1
  • 8
  • 2
    Possible duplicate of [How do I query between two dates using MySQL?](http://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql) – Vanquished Wombat Nov 18 '16 at 07:59
  • i dont have the between dates, I mean i have them but those are in the database. I can only make the query using the user entered database because there could be many classes and ranges ranges where my desired date falls. – Ramsha Khan Nov 18 '16 at 08:11
  • 1
    Are you using MySQL or MS SQL Server, or perhaps Sqlite? Don't tag products not involved! (Don't these 3 handle dates differently?) – jarlh Nov 18 '16 at 08:13
  • I didnt tag sql server, maybe it got tagged by mistake. Extremely sorry. For the rest, they may handle the dates differently but the query structure is somewhat the same. Btw i am using sqlite at the moment. @jarlh – Ramsha Khan Nov 18 '16 at 08:16
  • Sqlite has no DATE datatype. Is your StartDate, EndDate columns NUMERIC or TEXT? Please provide DDL and DML INSERT sample data. – Serg Nov 18 '16 at 08:24
  • @Serg my columns are date datatype however it is stored as text date, not numeric. The sample data is given above, do you mean you need a sample query or anything? – Ramsha Khan Nov 18 '16 at 08:34

2 Answers2

2

This works

CREATE TABLE "classes" (
    `Name`  TEXT,
    `Class` TEXT,
    `StartDate` TEXT,
    `EndDate`   TEXT
);
Insert into Classes(`Name`,`Class`,`StartDate`,`EndDate`)
values ("Maths","Lecture","2016-12-10","2016-12-30");
select * 
from Classes 
where "2016-12-20" between StartDate and EndDate;

Do not forget to check the input strings are representing valid dates in the choosen format ('YYYY-mm-DD')

Serg
  • 22,285
  • 5
  • 21
  • 48
-2

Try below query

            SELECT *
            FROM `TaleName`
            WHERE 
            StartDate >='2016-12-25' 
            AND EndDate <='2016-12-25 '
Mr. Bhosale
  • 3,018
  • 1
  • 17
  • 34
  • use only StartDate ='2016-12-25' will disaply all records started on that day. or what error you have – Mr. Bhosale Nov 18 '16 at 08:15
  • Surely back to front – Strawberry Nov 18 '16 at 08:16
  • it does not give an error, it just doesnt display any record when searched using this query. Using startDate only will not give the result because the StartDates in the database are different from the current user specified date :( – Ramsha Khan Nov 18 '16 at 08:19