Results 1 to 6 of 6

Thread: Ase 12.5

  1. #1
    Join Date
    Feb 2004
    Posts
    6

    Question Unanswered: Ase 12.5

    I want to get data from a table wich has a datetime field like this:

    Select * from table_name where datetimefield = @var_date...

    Considering that the field in database stores the full date and time value('2004-02-06 09:08:12'), and the variable var_date is type date ('2004-02-06'). How can I get the transactions made in a specific date?... Avoiding a full table scan because of use of convert function.. Any idea?...

    I usually use convert(char(10),datetimefield,110) for this purpose but it is not good for performance.

    Thanks in advance!!

  2. #2
    Join Date
    Jan 2003
    Location
    France - Nancy
    Posts
    26
    Hi,

    Make the convert on the variable instead of on the field.

    Use two variables (day and day+1) and a between, like that :

    select a,b,c, from mytable where myfield between convert(day,...) and convert(dayplusone,...)

    Carbone.

  3. #3
    Join Date
    Feb 2004
    Posts
    6
    Originally posted by Carbone
    Hi,

    Make the convert on the variable instead of on the field.

    Use two variables (day and day+1) and a between, like that :

    select a,b,c, from mytable where myfield between convert(day,...) and convert(dayplusone,...)

    Carbone.
    Thanks, i have used that too. But the query plan shows me a full scan...(note: the indexes exist, the statistics are updated). What happens when the i need to campare or join two tables with same criterium????...

  4. #4
    Join Date
    Jan 2003
    Location
    France - Nancy
    Posts
    26
    OK,

    one day is a small or a big part of your table ? (is this criteria determinent?)
    Do you make update index statistics or just update statistics ?

  5. #5
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    There are three ways.

    a) convert
    b) where datecol >= date1 and datecol <= date2
    // start with 00 hours on date1 and 23:59:59 on date2
    c) Create a redundant DATE column .. redundantcolumn = convert(varchar(8),date_column,1)

    Option b) should use the index. If its doing a scan, as Carbone mentioned, update the stats.

    Thanks,
    Vishi

  6. #6
    Join Date
    Feb 2004
    Posts
    6
    Originally posted by trvishi
    There are three ways.

    a) convert
    b) where datecol >= date1 and datecol <= date2
    // start with 00 hours on date1 and 23:59:59 on date2
    c) Create a redundant DATE column .. redundantcolumn = convert(varchar(8),date_column,1)

    Option b) should use the index. If its doing a scan, as Carbone mentioned, update the stats.

    Thanks,
    Vishi

    Thank you!!...
    Yes I have update index and table statistics...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •