Re: Selecting rows by DATE ranges
On Monday 25 June 2007 10:32, Mogens Melander wrote: > Looks like you have datetime fields makeing > "2007-01-01 00:00:01" > "2007-01-01". Or use SELECT ... WHERE CONVERT(date,DATE)>"2007-01-01"... if 'date' is a DATETIME field. > > Also using BETWEEN on date-ranges might help. > > On Sun, June 24, 2007 02:13, Miguel Cardenas wrote: > > Hello list > > > > I found a little problem with an application am developing, in particular > > creating reports by DATE ranges. > > > > Examples: > > > > select ... where date>"2007-01-01"; > > returns all records where date is greater (and equal inclusive) to > > 2007-01-01 > > > > select ... where date>="2007-01-01"; > > returns all records where date is greater/equal to 2007-01-01 > > > > the ">" and ">=" have the same effect > > > > select ... where date>"2007-01-01" and date<"2007-01-20"; > > returns all records where date is greater/equal to 2007-01-01 and less > > than "2007-01-20" > > *** DOES NOT RETURN RECORDS FROM DAY *20* > > > > select ... where date>"2007-01-01" and date<="2007-01-20"; > > returns all records where date is greater/equal to 2007-01-01 and less > > than "2007-01-20" although I'm using "<=" > > *** DOES NOT RETURN RECORDS FROM DAY *20* > > > > My doubts are: > > > > 1. how can I retrieve rows with a date>"..." NOT INCLUDING the day of the > > specified date, I mean apply a strict GREATHER THAN > > > > 2. how can I retrieve rows with date<="..." INCLUDING the day of the > > specified > > date. currently I have to do a date>="date1" and date<="date2+1day" > > > > I need to retrieve rows in this way > > > > date>X > > date>=X > > date > date<=X > > date>X and date > date>=X and date > date>=X and date<=Y > > date>X and date<=Y > > > > and so... didn't find a function to specify ranges of dates and the LESS > > THAN/EQUAL operator does not include the last day, so my reports with > > "<=" are done by adding one day but don't like to use it this way since > > it could > > be confusing and generate errors on reports. > > > > Thanks for any comment, > > Miguel > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > -- > > This message has been scanned for viruses and > > dangerous content by MailScanner, and is > > believed to be clean. > > -- > Later > > Mogens Melander > +45 40 85 71 38 > +66 870 133 224 > > > > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting rows by DATE ranges
Looks like you have datetime fields makeing "2007-01-01 00:00:01" > "2007-01-01". Also using BETWEEN on date-ranges might help. On Sun, June 24, 2007 02:13, Miguel Cardenas wrote: > Hello list > > I found a little problem with an application am developing, in particular > creating reports by DATE ranges. > > Examples: > > select ... where date>"2007-01-01"; > returns all records where date is greater (and equal inclusive) to > 2007-01-01 > > select ... where date>="2007-01-01"; > returns all records where date is greater/equal to 2007-01-01 > > the ">" and ">=" have the same effect > > select ... where date>"2007-01-01" and date<"2007-01-20"; > returns all records where date is greater/equal to 2007-01-01 and less > than "2007-01-20" > *** DOES NOT RETURN RECORDS FROM DAY *20* > > select ... where date>"2007-01-01" and date<="2007-01-20"; > returns all records where date is greater/equal to 2007-01-01 and less > than "2007-01-20" although I'm using "<=" > *** DOES NOT RETURN RECORDS FROM DAY *20* > > My doubts are: > > 1. how can I retrieve rows with a date>"..." NOT INCLUDING the day of the > specified date, I mean apply a strict GREATHER THAN > > 2. how can I retrieve rows with date<="..." INCLUDING the day of the > specified > date. currently I have to do a date>="date1" and date<="date2+1day" > > I need to retrieve rows in this way > > date>X > date>=X > date date<=X > date>X and date date>=X and date date>=X and date<=Y > date>X and date<=Y > > and so... didn't find a function to specify ranges of dates and the LESS > THAN/EQUAL operator does not include the last day, so my reports with "<=" > are done by adding one day but don't like to use it this way since it > could > be confusing and generate errors on reports. > > Thanks for any comment, > Miguel > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting rows by DATE ranges
date(), date_format() t Miguel Cardenas írta: Hello list I found a little problem with an application am developing, in particular creating reports by DATE ranges. Examples: select ... where date>"2007-01-01"; returns all records where date is greater (and equal inclusive) to 2007-01-01 select ... where date>="2007-01-01"; returns all records where date is greater/equal to 2007-01-01 the ">" and ">=" have the same effect select ... where date>"2007-01-01" and date<"2007-01-20"; returns all records where date is greater/equal to 2007-01-01 and less than "2007-01-20" *** DOES NOT RETURN RECORDS FROM DAY *20* select ... where date>"2007-01-01" and date<="2007-01-20"; returns all records where date is greater/equal to 2007-01-01 and less than "2007-01-20" although I'm using "<=" *** DOES NOT RETURN RECORDS FROM DAY *20* My doubts are: 1. how can I retrieve rows with a date>"..." NOT INCLUDING the day of the specified date, I mean apply a strict GREATHER THAN 2. how can I retrieve rows with date<="..." INCLUDING the day of the specified date. currently I have to do a date>="date1" and date<="date2+1day" I need to retrieve rows in this way date>X date>=X dateX and date=X and date=X and date<=Y date>X and date<=Y and so... didn't find a function to specify ranges of dates and the LESS THAN/EQUAL operator does not include the last day, so my reports with "<=" are done by adding one day but don't like to use it this way since it could be confusing and generate errors on reports. Thanks for any comment, Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting rows by DATE ranges
Hello list I found a little problem with an application am developing, in particular creating reports by DATE ranges. Examples: select ... where date>"2007-01-01"; returns all records where date is greater (and equal inclusive) to 2007-01-01 select ... where date>="2007-01-01"; returns all records where date is greater/equal to 2007-01-01 the ">" and ">=" have the same effect select ... where date>"2007-01-01" and date<"2007-01-20"; returns all records where date is greater/equal to 2007-01-01 and less than "2007-01-20" *** DOES NOT RETURN RECORDS FROM DAY *20* select ... where date>"2007-01-01" and date<="2007-01-20"; returns all records where date is greater/equal to 2007-01-01 and less than "2007-01-20" although I'm using "<=" *** DOES NOT RETURN RECORDS FROM DAY *20* My doubts are: 1. how can I retrieve rows with a date>"..." NOT INCLUDING the day of the specified date, I mean apply a strict GREATHER THAN 2. how can I retrieve rows with date<="..." INCLUDING the day of the specified date. currently I have to do a date>="date1" and date<="date2+1day" I need to retrieve rows in this way date>X date>=X dateX and date=X and date=X and date<=Y date>X and date<=Y and so... didn't find a function to specify ranges of dates and the LESS THAN/EQUAL operator does not include the last day, so my reports with "<=" are done by adding one day but don't like to use it this way since it could be confusing and generate errors on reports. Thanks for any comment, Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
