locked
SQL select range of dates RRS feed

  • Question

  • User1418733979 posted

    SELECT * FROM dally_report WHERE Empdate BETWEEN '16-08-2016' AND '20-08-2016 This is correct data is display 

    But

    SELECT * FROM dally_report WHERE Emp_date BETWEEN '29-08-2016' AND '18-09-2016

    I did type different month  09 data can't display in crustal report what is that?

    Tuesday, August 16, 2016 2:26 PM

Answers

  • User-707554951 posted

    hi hewawasam'

    As for this issue, it seems that you have post a similar thread in this forum :

    http://forums.asp.net/t/2101560.aspx

    I suggest you could refer to my reply in that thread. And try to use the following sql statement to convert varchar to Datetime, then filter data.

    select * from mytable where PARSE( SDate AS datetime using 'it-IT') between PARSE('14-08-2016' AS datetime using 'it-IT') and PARSE('21-09-2016' AS datetime using 'it-IT' )

    Hope this can help you. If you have any question and confusion about the problem. Please don't hesitate to let me know.

    Best regards
    Cathy

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 19, 2016 5:27 AM

All replies

  • User475983607 posted

    BETWEEN returns true when the date fall within the two parameters.  In your statement that is 29-08-2016 at 12:00AM and 18-09-2016 at 12 AM.  The query will not return dates 18-09-2016 where the time portion of the date time is greater than 12 AM.

    Please see the T-SQL documentation for more information.

    https://msdn.microsoft.com/en-us/library/ms187922.aspx

    Tuesday, August 16, 2016 2:37 PM
  • User1418733979 posted

    Could you please send me a code sample above this code please dear

    Tuesday, August 16, 2016 3:06 PM
  • User475983607 posted

    hewawasam

    Could you please send me a code sample above this code please dear

    BETWEEN '29-08-2016' AND '19-09-2016

    Tuesday, August 16, 2016 3:08 PM
  • User1418733979 posted

    MySqlDataAdapter da = new MySqlDataAdapter("SELECT Emp_name,Emp_date,Total FROM dally_report WHERE Emp_date BETWEEN '" + txt_startdate.Text.ToString() + "' AND '" + txt_enddate.Text.ToString() + "' ", DBConnection.connection());
    DataSet ds = new DataSet();
    da.Fill(ds);
    ReportDocument rd = new ReportDocument();
    rd.Load(Server.MapPath("Report2.rpt"));
    rd.SetDataSource(ds.Tables[0]);
    CrystalReportViewer1.ReportSource = rd;

    Moth value not detected

    I did type range of  18-08-2-16 to 20-08-2016 its correct but i typed  18-08-2-16 to 25-08-2016 then display September value please give a solution   

    Tuesday, August 16, 2016 5:02 PM
  • User475983607 posted

    I did type range of  18-08-2-16 to 20-08-2016 its correct but i typed  18-08-2-16 to 25-08-2016 then display September value please give a solution   

    Are you sure the data format is dd-MM-yyyy and not MM-dd-yyyy?

    Tuesday, August 16, 2016 5:16 PM
  • User1418733979 posted

    Yes its ok dear..... what is that and my data type is VARCHAR (20)

    Tuesday, August 16, 2016 5:27 PM
  • User475983607 posted

    hewawasam

    Yes its ok dear..... what is that

    It's impossible to determine give the information.  Have you tried querying the database directly in SSMS and comparing that query with the dynamically created query?

    Have you tried stepping through you r code with the debugger?

    Tuesday, August 16, 2016 5:33 PM
  • User1418733979 posted

    My column data type is varchar ?

    Tuesday, August 16, 2016 5:38 PM
  • User3690988 posted

    If your data type is varchar, you are not comparing dates but strings.  If you want to select by a date, your data type should be a date. 

    Tuesday, August 16, 2016 5:57 PM
  • User475983607 posted

    My column data type is varchar ?

    I did not see your edit.  

    No wonder it does not work!  VARCHAR is a string.   It would be best to use the correct type , a DATETIME, in the table.  Otherwise you can convert the VARCHAR to a date type but that creates extra work.

    Tuesday, August 16, 2016 7:03 PM
  • User-707554951 posted

    hi hewawasam'

    As for this issue, it seems that you have post a similar thread in this forum :

    http://forums.asp.net/t/2101560.aspx

    I suggest you could refer to my reply in that thread. And try to use the following sql statement to convert varchar to Datetime, then filter data.

    select * from mytable where PARSE( SDate AS datetime using 'it-IT') between PARSE('14-08-2016' AS datetime using 'it-IT') and PARSE('21-09-2016' AS datetime using 'it-IT' )

    Hope this can help you. If you have any question and confusion about the problem. Please don't hesitate to let me know.

    Best regards
    Cathy

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 19, 2016 5:27 AM