locked
SQL - CONVERT & BETWEEN RRS feed

  • Pergunta

  • Hi All Smile

    Can you show me a way to be able to do this in a SQL Access string ?

    (Note , the field type is Text, so the use of the CONVERT , but don't work on access)

    HOW-TO :

    SELECT * FROM YourTable WHERE CONVERT(datetime, datecolum) BETWEEN '01-01-2005' AND '04-19-2009'

    Thank you veryyy much !!

    Core regards
    terça-feira, 29 de abril de 2008 20:19

Respostas

  • Hi,

     

    You can use the format function. Ex:

     

    SELECT * FROM YourTable WHERE FORMAT(datecolum,'dd-mm-yyyy') BETWEEN '01-01-2005' AND '04-19-2009'

    I don't think is a good idea to use functions in the WHERE clause. They tend to make the performance of the query slow down.

     

    [ ]s,

     

    Gustavo

    terça-feira, 29 de abril de 2008 20:45

Todas as Respostas

  • Hi,

     

    You can use the format function. Ex:

     

    SELECT * FROM YourTable WHERE FORMAT(datecolum,'dd-mm-yyyy') BETWEEN '01-01-2005' AND '04-19-2009'

    I don't think is a good idea to use functions in the WHERE clause. They tend to make the performance of the query slow down.

     

    [ ]s,

     

    Gustavo

    terça-feira, 29 de abril de 2008 20:45
  • Great!

    An altertive way suggested by Adam Haines could be :


    Code Snippet

    select *

    from mytable

    where cdate(mydatecolumn) between #01-01-2005# and #04-19-2009#

     

     

     

    Best way in your opnion ?

    Regards

    terça-feira, 29 de abril de 2008 22:20
  • Hi,

     

    depending on your datecolumn format, I would prefer to use DateSerial function instead of CDate. If it is formatted as month/day/year, CDate will work fine.

     

    quarta-feira, 30 de abril de 2008 00:11
    Moderador
  • Hi,

     

    I think you must test the solutions to discovery which can perform better. The problem with both solutions is that they use a function against a column in the table. When we use constructions like that, is necessary to read the whole table, convert the column in the function and compare the results to collect the rows that satisfy the where clause. Even if we put an index it won't help in this case. When the table is small that's no problem but if the table is very large the performance can slow down.

     

    The best solution is one that doesn't use functions against columns. We can put functions against a value but not against a column. Unfortunally this is not always possible. If you can make a query like the construction down here, it will perform better

     

    select * from mytable where mydatecolumn between 'Value1' and 'Value2'

     

    I know that is possible to do it in some databases (like SQL Server) using the ISO format (YYYYMMDD) but I'm not sure if it is possible in Access.

     

    Gustavo

    quarta-feira, 30 de abril de 2008 12:01