Query to select data between two dates with the format m d yyyy

Question

I am facing problem when i m trying to select records from a table between two dates   m using the following query  select   from xxx where dates between  10 10 2012  and  10 12 2012    this query works for me but when the dates are in format like 1 1 2013   it doesn t work    plz solve my problem ASAP

User · Answer

Try this  SELECT    FROM xxx  WHERE dates BETWEEN STR TO DATE  10 10 2012     m  d  Y      AND STR TO DATE  10 12 2012     m  d  Y        or   SELECT    FROM xxx  WHERE STR TO DATE dates     m  d  Y   BETWEEN STR TO DATE  10 10 2012     m  d  Y      AND STR TO DATE  10 12 2012     m  d  Y

User · Answer

SELECT   FROM tablename WHERE STR TO DATE columnname    d  m  Y     BETWEEN STR TO DATE  29 05 2017     d  m  Y       AND STR TO DATE  30 05 2017     d  m  Y     It works perfectly

User · Answer

Date3   date  y-m-d     Date2   date  y-m-d   strtotime  -7 days         SELECT   FROM disaster WHERE date BETWEEN     Date2    AND      Date3

User · Answer

select   from xxx where dates between  2012-10-10  and  2012-10-12    I always use YYYY-MM-DD in my views and never had any issue  Plus  it is readable and non equivocal  You should be aware that using BETWEEN might not return what you expect with a DATETIME field  since it would eliminate records dated  2012-10-12 08 00  for example  I would rather use where dates  gt    2012-10-10  and dates  lt   2012-10-13   lower than next day

User · Answer

By default Mysql store and return    date    data type values in    YYYY MM DD    format   So if we want to display date in different format then we have to format date values as per our requirement in scripting language  And by the way what is the column data type and in which format you are storing the value

User · Answer

Try this   select   from xxx where dates between convert datetime  10 10 2012  103  and convert dattime  10 12 2012  103

User · Answer

DateTime dt1   this dateTimePicker1 Value Date  DateTime dt2   this dateTimePicker2 Value Date AddMinutes 1440   String query    SELECT   FROM student WHERE sdate BETWEEN      dt1      AND      dt2

User · Answer

you have to split the datetime and then store it with your desired format like dd MM yyyy  then you can use this query with between but i have objection using this becasue it will search every single data on your database so i suggest you can use datediff           Dim start   txtstartdate Text Trim           Dim endday   txtenddate Text Trim           Dim arr           arr   Split start               Dim dt As New DateTime         dt   New Date Val arr 2  ToString   Val arr 1  ToString   Val arr 0  ToString           Dim arry           arry   Split endday               Dim dt2 As New DateTime         dt2   New Date Val arry 2  ToString   Val arry 1  ToString   Val arry 0  ToString            qry    SELECT   FROM  calender  WHERE datediff day     amp  dt  amp      date   gt  0 and datediff day     amp  dt2  amp      date   lt  0     here i have used dd MM yyyy format

User · Answer

use this   select   from xxx where dates between  10 oct 2012  and  10 dec 2012    you are entering string  So give the name of month as according to format

User · Answer

This solution provides CONVERT IMPLICIT operation for your condition in predicate  SELECT    FROM xxx  WHERE CAST dates AS date  BETWEEN  1 1 2013  and  1 2 2013      OR   SELECT    FROM xxx  WHERE CONVERT date  dates  101  BETWEEN  1 1 2013  and  1 2 2013      Demo on SQLFiddle

[sql] Query to select data between two dates with the format m/d/yyyy

Examples related to sql

Examples related to database

Examples related to sql-server-2008