locked
Convert and select rows between a date RRS feed

  • Question

  • User1646833270 posted

    Hi.

    I have stored strings in this format in sql server: 23/12/2015

    How to make a SELECT statement so as to select rows between 2 dates?

    Thank you.

    Friday, January 8, 2016 7:12 AM

Answers

  • User1646833270 posted

    I found a solution using cast.

    "set dateformat dmy SELECT blabla WHERE thedate BETWEEN cast ('date1' as datetime) AND cast ('date2' as datetime)"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, January 9, 2016 7:19 PM

All replies

  • User753101303 posted

    Hi,

    This is really stored as a string in your database? If yes don't. Always use the proper data type. Don"t care about how it is shown by SQL Server. You can format the same date as you wish depending on the language in which you want to show it. Here at best you would need to do complex string operation or to convert back anyway this string to a date to be able to do a simple selection.

    With a real date, and using parameterized queries - see http://www.csharp-station.com/Tutorial/AdoDotNet/Lesson06 - you could just use something such as ;

    WHERE MyDate BETWEEN @Start AND @Finish and you'll pass date values to the query.

    Or :

    WHERE MyDate>=@Start AND MyDate<DATEADD(day,1,@Finish) (if you use a datetime rather than just a date, when giving the end date you usually expect data to be taken until the end of this day (that is before the very beginning of the next day).

    Friday, January 8, 2016 7:47 AM
  • User-62323503 posted

    As per my understanding your date is stored as string in 'dd/mm/yyyy' format.

    Use convert function to understand the format and cast it to date 

    select * from table where convert(date,datecolumn,103) between @fromdate and @todate


     

    Friday, January 8, 2016 1:06 PM
  • User1646833270 posted

    I found a solution using cast.

    "set dateformat dmy SELECT blabla WHERE thedate BETWEEN cast ('date1' as datetime) AND cast ('date2' as datetime)"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, January 9, 2016 7:19 PM
  • User753101303 posted

    If thedate is really still a string you likely have also an implicit cast to a datetime and for example any index on this string column won't be used to speed up the selection.

    Sunday, January 10, 2016 9:28 PM