fullyscintilla Posted June 5, 2009 Share Posted June 5, 2009 hi I need a bit of help.. i'm working on search app written in php and it all but working aside one setback. I seem to be having trouble properly searching for dates in the database. I have a form setup to allow the search between two dates. My SQL query: SELECT * FROM ".$table_name." WHERE enrollment_date>='2008-1-1' AND enrollment_date<='2009-12-31' But I seem to be getting mixed results.. is there a better way to search a db for dates? ~Thanx Quote Link to comment Share on other sites More sharing options...
fullyscintilla Posted June 5, 2009 Author Share Posted June 5, 2009 this isnt a syntax problem as it works. Maybe mixed results isn't the right term.. the results aren't really mixed. its the same result each time. the problem is it seems to be skipping dates or for some reason excluding them. Say for instance in a db of 10 it's only returning 8 results. Even tho all the dates are between the dates searched. I cant figure out why its happening. I can switch to a single date query and locate each result. Its just the range query. Quote Link to comment Share on other sites More sharing options...
trq Posted June 5, 2009 Share Posted June 5, 2009 What data type is your enrollment_date field? Quote Link to comment Share on other sites More sharing options...
fullyscintilla Posted June 7, 2009 Author Share Posted June 7, 2009 I thought it was DATE but i guess it is VARCHAR.. this is probably the issue isn't it.. Is there any way that I can keep YYYY-MM-DD format without leading zeros? Another VXML app is using the DB and may not be able to change it. thanks Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted June 7, 2009 Share Posted June 7, 2009 It should be in some DATE format. You suggest you change it. I'm not sure how your other app handles it, but it's definitely is not a good set up. Quote Link to comment Share on other sites More sharing options...
fenway Posted June 7, 2009 Share Posted June 7, 2009 There are no "leading zeroes" in a date field... Quote Link to comment Share on other sites More sharing options...
fullyscintilla Posted June 7, 2009 Author Share Posted June 7, 2009 umm.. yes there are.. and Mysql forces you to use them. I wasn't able to change the column data type to DATE without adding them. Quote Link to comment Share on other sites More sharing options...
fullyscintilla Posted June 7, 2009 Author Share Posted June 7, 2009 It should be in some DATE format. You suggest you change it. I'm not sure how your other app handles it, but it's definitely is not a good set up. I'm not sure what you mean here. The data itself is already in a "date" format of yyyy-mm-dd. The data "type" for the column in the DB wasn't set to DATE. I've changed that now. I was just wondering if anyone knew of a way to not have to use Leading zeros. 2008-03-23 < leading zeros 2008-3-23 < no leading zeros @fenway: maybe you could elaborate on that a bit for me? There are no leading zeros in a date? Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted June 7, 2009 Share Posted June 7, 2009 SELECT column FROM table WHERE date BETWEEN date1 AND date2; You can use BETWEEN for dates. Quote Link to comment Share on other sites More sharing options...
fullyscintilla Posted June 8, 2009 Author Share Posted June 8, 2009 SELECT column FROM table WHERE date BETWEEN date1 AND date2; You can use BETWEEN for dates. thanks I'll have to use that. Quote Link to comment Share on other sites More sharing options...
fenway Posted June 8, 2009 Share Posted June 8, 2009 I don't consider those "leadings zeroes" vs. zerofill. Quote Link to comment Share on other sites More sharing options...
fullyscintilla Posted June 8, 2009 Author Share Posted June 8, 2009 I don't consider those "leadings zeroes" vs. zerofill. I'm sorry can you speak in full sentences atleast? btw you are spelling "zero" wrong. Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted June 9, 2009 Share Posted June 9, 2009 He didn't spell zero incorrectly. Google zero-fill if you don't know what it is. Quote Link to comment Share on other sites More sharing options...
fenway Posted June 9, 2009 Share Posted June 9, 2009 1) I'm not spelling anything wrong -- see here. 2) That was a full sentence; "leading" makes it sound optional; the sql-99 date format is YYYY-MM-DD, so there's no choice here, really. Quote Link to comment Share on other sites More sharing options...
fullyscintilla Posted August 23, 2009 Author Share Posted August 23, 2009 Ok so "technically" you didn't spell it wrong because its in the dictionary as a "Slang" way to spell zero as plural. But the proper way is still "zeros". I looked up zerofill and it comes up with absolutley nothing. Besides referring to a slang term for a low level format on a hdd. So obviously that term isn't even related to this questioning. YES "leading" does make it sound optional.. Cuz the data I was given the leading zero was not given. I was trying to find out a way I could make it work you guys decided to be smartbutts and instead of really helping me your just laughing at the fact that you think i'm retarded. When in reality I am not. So thanks for that. That was a full sentence, but not a full thought. 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.