CODEWITHSUNDEEP
mysqlsqldatetime
Jer_TX
Jer_TX

Reputation: 474

SQL search between two days without datetime

Trying to search in a database a date range. Problem is, I cannot use the datetime column type in my database. To compensate, date's are displayed as three columns. a Month column, a Day column, and a Year column. Here is my SQL query:

SELECT COUNT(*) 
  FROM `import` 
 WHERE `call_day` BETWEEN 29 AND 15 
   AND `call_month` BETWEEN 9 AND 10 
   AND `call_year` BETWEEN 2013 AND 2013

You can see where I run into trouble. call_day needs to search between the 29th day and the 15th day. This won't work because 15 is smaller than 29, but I need it to work because the month is in the future :)

Any thoughts/solutions? No I cannot change the database in any way. Read only.

Upvotes: 4

Views: 212

Answers (4)

peterm
peterm

Reputation: 92795

Besides the concatenation approach, which can be implemented in quite a few ways, e.g.

SELECT * 
  FROM import
 WHERE STR_TO_DATE(CONCAT_WS('-', call_year, call_month, call_day), '%Y-%c-%e') 
       BETWEEN '2013-09-29' AND '2013-10-15'

or

SELECT *
  FROM import
 WHERE CONCAT(call_year, LPAD(call_month, 2, '0'), LPAD(call_day, 2, '0'))
       BETWEEN '20130929' AND '20131015'

Here is SQLFiddle demo

that will always cause a full scan and assuming that date ranges in your queries usually don't span more than a few months you can also do

SELECT *
  FROM import
 WHERE (call_year = 2013 AND 
        call_month = 9   AND 
        call_day BETWEEN 29 AND DAY(LAST_DAY('2013-09-01'))) -- or just 30
    OR (call_year = 2013 AND 
        call_month = 10 AND 
        call_day BETWEEN  1 AND 15)

Here is SQLFiddle demo

For a query that spans a year (e.g. from 2012-08-20 to 2013-10-15)

SELECT * 
  FROM import
 WHERE (call_year = 2012 AND 
        call_month = 8 AND
        call_day BETWEEN 20 AND 31)
    OR (call_year = 2012 AND 
        call_month BETWEEN 9 AND 12 AND
        call_day BETWEEN 1 AND 31)
    OR (call_year = 2013 AND 
        call_month BETWEEN 1 AND 9 AND 
        call_day BETWEEN  1 AND 31)
    OR (call_year = 2013 AND 
        call_month = 10 AND
        call_day BETWEEN 1 AND 15)

Here is SQLFiddle demo

Upvotes: 3

Andrew
Andrew

Reputation: 8758

As PeterM said, this may play hell with performance, but if you're storing these as integers, you can do the following: (year * 10000) + (month* 100) + day will always yield an 8 byte "date".

2013 *10000 = 20130000
9 * 100 =          900
                    15
              20130915

It's an ugly hack, and it will be expensive, because you'll be evaluating every row in your table, if you can't limit the rows in any other way, but I think it'll work.

EDIT: Typing is hard!

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

SELECT COUNT(*) FROM `import` 
WHERE CONCAT(CAST(`call_year`AS CHAR(4)) , RIGHT(CONCAT('00',CAST(`call_month`AS CHAR(2))),2) , RIGHT(CONCAT('00',CAST(`call_day`AS CHAR(2))),2))
BETWEEN '20130929' AND '20131015'

Upvotes: 0

Jonysuise
Jonysuise

Reputation: 1830

Concat the values like yyyymmdd and then you can compare them like strings.

Upvotes: 5

Related Questions