CODEWITHSUNDEEP
sqlsybase
LPP
LPP

Reputation: 77

Sybase - Filter records with current date efficiently

I am trying to filter records based on current date (date part only) against a column "date_sent" which is a DateTime datatype. Though this can be written in multiple ways, I want to know which method will be the most efficient. The table has 11-12 millions of records ant any given time.

Let's say the current date is 16th April 2018

SELECT *
FROM TABLE_NAME
WHERE datepart(YEAR, date_sent) = 2018
  AND datepart(MONTH,date_sent) = 4
  AND datepart(DAY,date_sent) = 16;

SELECT *
FROM TABLE_NAME
WHERE convert(char(8), date_sent, 112) = convert(char(8), getdate(), 112);

Any other suggestions.

Upvotes: 0

Views: 3034

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

I would start with:

select *
from table_name
where date_sent >= dateadd(day, datediff(day, 0, getdate()), 0) and
      date_sent < dateadd(day, 1 + datediff(day, 0, getdate()), 0)

The right hand side removes the time component of the current date. The comparisons should allow Sybase to still us an index on date_sent.

EDIT:

Perhaps Sybase doesn't permit 0 as a date value. You can also do:

select *
from table_name
where date_sent >= dateadd(day, datediff(day, cast('2000-01-01' as date), getdate()), cast('2000-01-01' as date)) and
      date_sent < dateadd(day, 1 + datediff(day, cast('2000-01-01' as date), getdate()), cast('2000-01-01' as date))

Upvotes: 1

Related Questions