gokhul Posted June 4, 2009 Share Posted June 4, 2009 hello, can anyone tell me how to get the result between the range of the following dates. name age gender joiningdate leavingdate xxx 10 male 04/23/09 05/22/09 yyy 11 female 01/02/09 08/02/09 zzz 12 male 04/26/09 04/28/09 How should I write the select statement such that I can get the details between any two given dates? Please help me. Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted June 4, 2009 Share Posted June 4, 2009 What type of data is joiningdata and leavingdate? I hope it's of some date format - DATE, TIMESTAMP, etc. I don't think MM/DD/YYYY is a MySQL date-formatted string. I would not use varchar and use TIMESTAMP or DATE or DATETIME. That should help when selecting. Quote Link to comment Share on other sites More sharing options...
gokhul Posted June 4, 2009 Author Share Posted June 4, 2009 yaa... i mean the joining date and the leaving date. i have the date format in varchar now. so if i change the datatype to date, what statement should i use to get the details? Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted June 4, 2009 Share Posted June 4, 2009 After you change your columns to a DATE data type (the format they are now cannot be directly used in a date comparison) what exactly are you looking for? Provide an example. Are you looking for the values in your table that are completely within two given dates or are you looking to match any that have any overlap at all with two given dates? Quote Link to comment Share on other sites More sharing options...
gokhul Posted June 4, 2009 Author Share Posted June 4, 2009 Yes..I am looking for the entire details in my database between any two given dates. I have the data in the following format. patient age gender consultdate 1 20 male 04/23/09 2 30 male 04/30/09 3 10 female 05/19/08 Sorry for giving the wrong data above. Now, If i say the range as 05/19/08 and 04/23/09....i should get all the three results. Can you tell me what should be my select statement. Regards. Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted June 4, 2009 Share Posted June 4, 2009 They're still not in date format. http://dev.mysql.com/doc/refman/5.0/en/datetime.html Quote Link to comment Share on other sites More sharing options...
gokhul Posted June 4, 2009 Author Share Posted June 4, 2009 Hi, i have changed the date format to yyyy-mm-dd now. Can anyone tell me how to write the select statement to get the details in the database between any two given dates. Regards Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted June 4, 2009 Share Posted June 4, 2009 SELECT cols FROM table WHERE date BETWEEN date1 AND date2; Quote Link to comment Share on other sites More sharing options...
gokhul Posted June 4, 2009 Author Share Posted June 4, 2009 Please stay back ken... i will just check it. Quote Link to comment Share on other sites More sharing options...
gokhul Posted June 4, 2009 Author Share Posted June 4, 2009 KEN.....THANKS a lot KEN. I owe a lot to you. its working. thanks once again. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.