CODEWITHSUNDEEP
sqlsql-server-2008
Nil
Nil

Reputation: 133

Getting the latest records according to the date

I have SQL table in SQL server 2008, and I want to get latest record which depends on its date.

I have written a query as

  SELECT TOP 1 * 
    FROM ProductPrice
   WHERE ProductID = 1698
     AND EffectiveFrom <= '1/31/2013'
ORDER BY EffectiveFrom DESC

and in the database there is a record which contains the Date 2013-01-31 12:12:49.000

Now I am expecting above query will return 1 record, but its not returning anything What should I change in the query ?

Upvotes: 1

Views: 170

Answers (4)

sgeddes
sgeddes

Reputation: 62861

The problem is you're comparing '1/31/2013 00:00' to '1/31/2013 12:12' -- it's less than that value. You need to convert your query to something like:

SELECT TOP 1 *  
FROM ProductPrice 
WHERE  ProductID = 1698 
   AND CONVERT(DATE,EffectiveFrom) <= '1/31/2013' 
ORDER BY EffectiveFrom DESC

Upvotes: 0

Justin Donohoo
Justin Donohoo

Reputation: 380

The key is that your date is a datetime... Unless you get rid of the timestamp through a cast/convert, or change your where clause. 2013-01-01 HH:MM:SS is always going to be > 01/01/2013.

  SELECT TOP 1 * 
    FROM ProductPrice
   WHERE ProductID = 1698
     AND CONVERT(CHAR(8), EffectiveFrom, 112) <= '20130101'
ORDER BY EffectiveFrom DESC

Upvotes: 1

TechDo
TechDo

Reputation: 18659

Please try:

  SELECT TOP 1 * 
    FROM ProductPrice
   WHERE ProductID = 1698
     AND EffectiveFrom <= (CONVERT(DATETIME, '1/31/2013') + 1)
ORDER BY EffectiveFrom DESC

It's not getting selected becoz datetime value 2013-01-31 12:12:49.000 is greater than 1/31/2013.

Upvotes: 0

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Because data in table contains not only date, but also time

  SELECT TOP 1 * 
    FROM ProductPrice
   WHERE ProductID = 1698
     AND CAST(EffectiveFrom AS date) <= '1/31/2013'
ORDER BY EffectiveFrom DESC

OR

  SELECT TOP 1 * 
    FROM ProductPrice
   WHERE ProductID = 1698
     AND EffectiveFrom <= DATEADD(SECOND, 86399, '1/31/2013')
ORDER BY EffectiveFrom DESC

Upvotes: 1

Related Questions