0

Say,i've to select a value from today to next sunday. How to input date?

Is there any keyword like FROM = 26-12-2001 TO = 30-12-2001

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
  • hope this helps http://stackoverflow.com/questions/1080207/mysql-select-all-data-between-two-dates – hungneox Dec 26 '11 at 13:06
  • Several good options already posted: my comment on use of BETWEEN: has to do with choice of data types. If you have a date/time field then when using between be careful of times and ensure what you enter is truly between that date/time. – xQbert Dec 26 '11 at 13:36

2 Answers2

3

You would typically use BETWEEN for this

WHERE YourDate BETWEEN '26-12-2001' AND '30-12-2001'

although other common comparison operators can be used as well

WHERE YourDate >= '26-12-2001' AND Yourdate < '31-12-2001'

Note the use of 31/12/2001 when using the lesser then comparison operator

Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146
  • The BETWEEN operator can cause real trouble with an end date in a comparison range when you're working with DATETIME data items. It's best to use the second alternative. – O. Jones Dec 26 '11 at 13:15
  • @OllieJones - Would that be MySQL specific? I haven't had any troubles using BETWEEN with DATETIME on SQL Server (yet). – Lieven Keersmaekers Dec 26 '11 at 13:16
  • MySQL dates are specified YYYY-MM-DD, not DD-MM-YYYY. – pilcrow Dec 26 '11 at 14:00
  • No, the between problem is not mySQL-specific. If your timestamps contain both a date and a time (that is, they mention times of day other than midnight), then you'll have the issue. For example. Timestamp 2011-12-26-17-45-00 is not BETWEEN 2011-12-25 AND 2011-12-26 (it's after 2011-12-26). But 2011-12-26-00-00-00 is in that range. True in all SQL systems, because BETWEEN is inclusive. I've been burned by this. – O. Jones Dec 26 '11 at 22:48
1

Use between. In your case it would between date1 and date2

SELECT * FROM your_table_name
 WHERE your_tables_date_column 
 BETWEEN date1 and date2

Account for time if recorded with:

SELECT * FROM your_table_name
 WHERE DATE_FORMAT(your_tables_date_column, '%Y-%m-%d 00:00:00') 
  /* Oracle trunc(col) equivalent */
 between date1 and date2

It works for strings too, e.g.

SELECT * FROM Persons
 WHERE lower(last_name)
 BETWEEN 'smith' AND 'webster'

and for numbers e.g.

SELECT * FROM Persons
 WHERE height
 BETWEEN 1 and 3 /* meters, i.e. exclude kids */

and you can also use the negative, e.g.

SELECT * FROM Persons
 WHERE lower(last_name)
 NOT BETWEEN 'smith' AND 'webster'

btw the literal answer to your questions is something like:

SELECT * from tbl where date_field BETWEEN
CURDATE() AND 
DATE_ADD(CURDATE(), INTERVAL (8 - IF(DAYOFWEEK(CURDATE())=1, 8, DAYOFWEEK(CURDATE()))) DAY) AS NEXTSUNDAY  

/* Watch for performance issues with indexes when using functions on columns */
Michael Durrant
  • 93,410
  • 97
  • 333
  • 497
  • +1 It'd be wise to note that `FUNC(col) BETWEEN a AND b` generally means that the SQL engine cannot use an index on `col`. – pilcrow Dec 26 '11 at 14:02