Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Don't get confused about the datatype - it has nothing to do with your problem. You see this if you remove the / in the dates - to get real numbers: 20/02/2013 is actually more than 21 million higher than 01/02/2014</p> <p>20/02/2013 = 20022013<br/> 01/02/2014 = 1022014 </p> <p>If your varchar has the numbers in the order of date, month, year - you must reorder them in the field or in the query to get the correct values, but if they are in year month day, you have a varchar that will sort just like a datetime. </p> <p><strong>SOLUTION 1:</strong> If you must leave it the way it is in the table, you must do this:</p> <p>Database table:</p> <pre><code>CREATE TABLE `dates` ( `date_varchar` varchar(15) DEFAULT NULL, `date_datetime` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 </code></pre> <p>Data: </p> <pre><code>date_varchar date_datetime 01/02/2013 2013-02-01 00:00:00 02/02/2013 2013-02-02 00:00:00 03/02/2013 2013-02-03 00:00:00 03/02/2014 2014-02-03 00:00:00 03/03/2013 2013-03-03 00:00:00 04/02/2013 2013-02-04 00:00:00 05/02/2013 2013-02-05 00:00:00 </code></pre> <p>The query:</p> <pre><code>SELECT * FROM dates WHERE STR_TO_DATE(date_varchar,'%d/%m/%Y') BETWEEN '2013-02-03' AND '2013-02-04' </code></pre> <p>Will return two records: </p> <pre><code>date_varchar date_datetime 03/02/2013 2013-02-03 00:00:00 04/02/2013 2013-02-04 00:00:00 </code></pre> <p><strong>SOLUTION 2:</strong></p> <p>To change the order in the varchar to year, month, date in the field. Se the table data below:</p> <pre><code>date_varchar date_datetime 2013/02/01 2013-02-01 00:00:00 2013/02/02 2013-02-02 00:00:00 2013/02/03 2013-02-03 00:00:00 2013/02/04 2013-02-04 00:00:00 2013/02/05 2013-02-05 00:00:00 2013/03/03 2013-03-03 00:00:00 2014/02/03 2014-02-03 00:00:00 </code></pre> <p>Now you can select :</p> <pre><code>SELECT * FROM dates WHERE date_varchar BETWEEN '2013/02/02' AND '2013/02/03' </code></pre> <p>Now the numbers are ordered in the same way as datetime - you can just remove the / and - to see the numbers. So varchars are sorted the same as date_time</p> <p>The query return the same two rows:</p> <pre><code>date_varchar date_datetime 2013/02/02 2013-02-02 00:00:00 2013/02/03 2013-02-03 00:00:00 </code></pre> <p><strong>SOLUTION 3:</strong></p> <p>This is not for you, because you don't want to change the table, but i include it to make this complete. By changing the datatype to datetime you are forced to put the numbers in correct order and you use this query to get a range between dates:</p> <pre><code>SELECT * FROM dates WHERE date_datetime BETWEEN '2013-02-03' AND '2013-02-04' </code></pre> <p>The same two record: </p> <pre><code>date_varchar date_datetime 03/02/2013 2013-02-03 00:00:00 04/02/2013 2013-02-04 00:00:00 </code></pre> <p><strong>EXAMPLE OF THE PROBLEM:</strong></p> <p>Now just selecting the dates as varchar returns - you guessed it - the very same two records like first query <strong>and some more</strong>, just like what is happening to you:</p> <p>Data: </p> <pre><code>date_varchar date_datetime 01/02/2013 2013-02-01 00:00:00 02/02/2013 2013-02-02 00:00:00 03/02/2013 2013-02-03 00:00:00 03/02/2014 2014-02-03 00:00:00 03/03/2013 2013-03-03 00:00:00 04/02/2013 2013-02-04 00:00:00 05/02/2013 2013-02-05 00:00:00 SELECT * FROM dates WHERE date_varchar BETWEEN '03/02/2013' AND '04/02/2013' date_varchar date_datetime 03/02/2013 2013-02-05 00:00:00 03/02/2014 2014-02-03 00:00:00 03/03/2013 2013-02-05 00:00:00 04/02/2013 2013-02-03 00:00:00 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. PONot accessing the value using between statement in sql
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USEray Balkanli
    1. USBingo
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUndeletion
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYour last query would also return e.g. '03/03/2013' and '03/02/2014' if found in the table.
      singulars
      1. PO
      1. USTerje D.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload