Big Date Problems

Results 1 to 4 of 4

Thread: Big Date Problems

  1. #1
    Join Date
    Dec 1969
    Posts
    716

    Default Big Date Problems

    Hiyas all,<BR><BR>I currently have my date stored in the database in seperate fields (Day, Month, Year). This has been working perfectly so far. What I need to do now is look up data in the database dependent on a "Day From: - Day To:" type query.<BR><BR>Essentially the user selects a "Name" from a drop-down list. They then enter the Date From: [DD][MM][YYYY] and the Date To:<BR>All records between these dates would then be pulled out.<BR><BR>The current (broken) SQL Query I am using is as follows:<BR><BR>SQLString2 = "SELECT * from Register WHERE Year &#060;= "& theyearto &" AND Year &#062;= "& theyearfrom &" AND Month &#060;= "& themonthto &" AND Month &#062;= "& themonthfrom &" AND Day &#060;= "& thedayto &" AND Day &#062;= "& thedayfrom &" AND Name = &#039"& Name &"&#039"<BR><BR>This works ok for some dates (which fooled me at first) but when I tried eg: From: 01/01/1999 To: 01/01/2000 Day: 13/09/1999 .. it didn&#039t find any records :/ .. I can now see why it doesn&#039t I just don&#039t know what to do to get around this =(<BR><BR>Note that "Day" "Month" and "Year" and "Name" correspond to the values in the Register table. <BR><BR>Apologies for the lack of formatting, i&#039m still fairly new to all this and haven&#039t got the hang of formatting SQL strings as they should be.<BR><BR>Hope someone can help,<BR><BR>Thanks in advance,<BR><BR>Justin

  2. #2
    Join Date
    Dec 1969
    Posts
    1,388

    Default RE: Big Date Problems

    That&#039s a tough one. I see several problems with this besides just the year thing. If you entered a start date of 19/1/99 to 14/6/99, you wouldn&#039t get any records because you are telling it to get days less than the from day yet larger than the start date. Since no days are less than 14 and greater than 19, you wouldn&#039t get anything back. What you&#039re going to have to do is look at the dates in parts. You first need to get the records from the start date to the end of the month (i.e 19/1/99 through 31/1/99) then do an OR statement and get all the cases from (themonthfrom+1) and (themonthto-1) to fill in the middle then another OR statement and get the days from the beginning of themonthto (1/6/99) through the day specified. This only correctly gives you the information if the years are the same. Below is an example that is not formatted correctly, but gives you an idea.<BR><BR>SQL = SELECT * FROM register WHERE (year = fromyear and month = frommonth and day &#062;= fromday and day &#060;= 31) OR (year = fromyear and month &#062;= frommonth+1 and month &#060;= tomonth-1) OR (year = toyear and month = tomonth and day &#060;= today)<BR><BR>If you are dealing with more than one year, you do it similar to the above, just changing a few things. See below<BR><BR>SQL = SELECT * FROM register WHERE (year = fromyear and month = frommonth and day &#062;= fromday and day &#060;= 31) OR (year = fromyear and month &#062;= frommonth+1) OR (year = toyear and month &#060;= tomonth-1) OR (year = toyear and month = tomonth and day &#060;= today)<BR><BR>This example on works if the years are only one year apart i.e. 1999 to 2000. For longer differences, you just need to fill in the middle with an extra OR statment of (year &#062;= fromyear+1 and year &#060;= to year-1). To figure out which SQL statement to run, just make some basic if statements that subtract the fromyear from the toyear. If it equals 0, run the first. If it equals 1, run the second, if it is more than 1, run the third one. <BR><BR>There may be some flaw in my logic here somewhere, but this is still the general direction you need to go.

  3. #3
    Join Date
    Dec 1969
    Posts
    716

    Default RE: Big Date Problems

    Thanks a lot Matt - will be trying this today :o)<BR><BR>Justin

  4. #4
    JustinS Guest

    Default RE: Big Date Problems

    Hiyas all,<BR><BR>Posting name changed due to another "justin" making an appearance on the board :o)<BR><BR>I am still having problems with this between dates query. The query itself is as follows:<BR><BR>SQLString2 = "SELECT * from Register WHERE"<BR>SQLString2 = SQLString2 & "(Year = "& theyearfrom &" <BR>SQLString2 = SQLString2 & "AND Month = "& themonthfrom &" <BR>SQLString2 = SQLString2 & "AND Day &#062;= "& thedayfrom &" <BR>SQLString2 = SQLString2 & "AND Day &#060;= 31)"<BR>SQLString2 = SQLString2 & "OR (Year = "& theyearfrom &"<BR>SQLString2 = SQLString2 & "AND Month &#062;= "& themonthfrom &" +1)"<BR>SQLString2 = SQLString2 & "OR (Year = "& theyearto &" <BR>SQLString2 = SQLString2 & "AND Month &#060;= "& themonthto &" -1)"<BR>SQLString2 = SQLString2 & "OR (Year = "& theyearto &" <BR>SQLString2 = SQLString2 & "AND Month = "& themonthto &" <BR>SQLString2 = SQLString2 & "AND Day &#060;= "& thedayto &")"<BR><BR>(If there are any syntax errors here pls ignore them as I had to reformat it to make it a bit easier to read on the board) =)<BR><BR>Now this kinda works. The actual date I was trying to bring up the information for in the database was;<BR><BR>Day: 14 <BR>Month: 09 <BR>Year: 1999<BR><BR>The FROM date was: 01/01/1999<BR>The TO date was: 13/09/1999<BR><BR>When this query is run the data IS pulled out of the database. A problem occurs (no matching records are found) when I change the Day of the TO date to anything BELOW what the day actually is. Ie in this example if I set the TO day to 12 no data is found.<BR><BR>I also tried changing the data in the database manually so that the date that was being looked up was 05/09/1999 - sure enough if I entered 5 or below into the TO "day" textbox and submitted - no data was found. Anything above and it was.<BR><BR>I&#039ve changed/tweaked/broken the SQL query lots of times but just can&#039t seem to find what is wrong with it =(<BR><BR>Any ideas anyone?<BR><BR>As always, thanks in advance for any help =)<BR><BR>Justin


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •