none
how to Getting records between two dates

    Question

  • Hi All,

    I am using Sql Server 2005 compact edition.

    I am writing a query for getting records between two dates as mentioned below.

    SELECT

     

    Reference,PostedDate,MessageID,Status FROM PostedDocTable WHERE posteddate >= '10/12/2009' and posteddate <= '10/17/2009'

    But the query is not working as expected and results are out of the range.

    Here i am giving one of the record values of the posteddate filed for your easy reference '2009-10-12 13:45:26.000' so that it may help you. I am using dd/MM/yyyy format

    please kindly help me. i am stuck up with this

    with Regards,

    satheen

    Tuesday, July 06, 2010 10:14 AM

Answers

  • Thanks Alberto Silva for your repply,

    I have solvd my problem by referring the CAST and CONVERT (Transact-SQL) from this site http://msdn.microsoft.com/en-us/library/ms187928.aspx.

    I have modified my query as below and it worked

    query =

    @"SELECT Reference,PostedDate,MessageID,Status FROM PostedDocTable WHERE Name='CreateNewInvoice' AND CONVERT(NVARCHAR(10),PostedDate,121) >= CONVERT(NVARCHAR(10),'" + Convert.ToDateTime(fromDate).ToString("yyyy-MM-dd") + @"',121) AND CONVERT(NVARCHAR(10),PostedDate,121) <= CONVERT(NVARCHAR(10),'" + Convert.ToDateTime(toDate).ToString("yyyy-MM-dd") + @"',121) ORDER BY POSTEDDATE ASC";

    Thanks and Regards,

    satheen

    • Marked as answer by Satheen Tuesday, July 06, 2010 11:09 AM
    Tuesday, July 06, 2010 11:03 AM

All replies

  • Hi,

    First, take this as a rule: You should never embed dates as strings in your SQL instruction, you should user parameters instead.

    You don't tell us which datatype is your PostedDate column: if it's a DateTime, use the parameters and to make the instruction more readable, use the BETWEEN operator instead of the ... > = ... AND ... <= ... . Take into account that if you don't provide the time portion of the reference date, 00:00 is assumed, so in your example, no rows from the day 17th October 2009 would be returned unless the record time was 00:00. If this is a problem, add one day to the bigger reference date and subtract one second to get 23:59:59
    However, if your PostedDate column is a nvarchar or similar, your instruction will never work. The only way to make it work is to force the yyyy/MM/dd format.


    Alberto Silva Microsoft MVP - Device Application Development - http://msmvps.com/AlbertoSilva moving2u - R&D Manager - Microsoft Certified Partner - Mobility Solutions - http://www.moving2u.pt
    Tuesday, July 06, 2010 10:55 AM
  • Thanks Alberto Silva for your repply,

    I have solvd my problem by referring the CAST and CONVERT (Transact-SQL) from this site http://msdn.microsoft.com/en-us/library/ms187928.aspx.

    I have modified my query as below and it worked

    query =

    @"SELECT Reference,PostedDate,MessageID,Status FROM PostedDocTable WHERE Name='CreateNewInvoice' AND CONVERT(NVARCHAR(10),PostedDate,121) >= CONVERT(NVARCHAR(10),'" + Convert.ToDateTime(fromDate).ToString("yyyy-MM-dd") + @"',121) AND CONVERT(NVARCHAR(10),PostedDate,121) <= CONVERT(NVARCHAR(10),'" + Convert.ToDateTime(toDate).ToString("yyyy-MM-dd") + @"',121) ORDER BY POSTEDDATE ASC";

    Thanks and Regards,

    satheen

    • Marked as answer by Satheen Tuesday, July 06, 2010 11:09 AM
    Tuesday, July 06, 2010 11:03 AM
  • Again, you should not concatenate strings as dates (or as numbers in other scenarios) on SQL statements. Using parameters would save you a lot of time and problems.

    Don't you agree that this way the instruction looks far better?

    "SELECT ... FROM ... WHERE PostedDate BETWEEN @startDate AND @endDate"

    This not only saves you conversion troubles but also enables you to prepare commands you repeat a lot to increase performance. BTW, you should use parameters also on INSERT, UPDATE and DELETE.

    You should also beware that on SQL Server (not SQL Server Compact), string concatenation is an important security flaw known as 'sql injection', which may lead to very unpredictable results.


    Alberto Silva Microsoft MVP - Device Application Development - http://msmvps.com/AlbertoSilva moving2u - R&D Manager - Microsoft Certified Partner - Mobility Solutions - http://www.moving2u.pt
    Tuesday, July 06, 2010 1:59 PM
  • thanks Alberto,

    your reply solved my problem!

    Friday, October 08, 2010 1:58 PM