Link to home
Start Free TrialLog in
Avatar of kimfink
kimfink

asked on

How to compare only date in SQL Server 2005

I want my result set in SQL to be selected on a specified date. So my storedproc has specifiedDate parameter which is of type NVARCHAR, and I convert my datetime colum to a NVARCHAR with the format 104 (dd.mm.yy). I do this because i don't want the time part in datetime to be used in the compare statement.
Her is an example of the SQL Where clause:
WHERE convert(nvarchar,ChemicalUsage.TankChemical.RegistrationDate,104) <= @SpecifiedDate

But since I compare to strings, only the first to digit in the date format (my date format are dd.mm.yyyy) are compared. For example if specifiedDate is '01.03.2008' a row with date '02.02.2008' will not be returned since it only compare the two first digits.

I have also another question:
How can I get the row with the date nearest to the specified date?
For example if my @specified date parameter is '04.03.2008', and in my database I have two rows one
with date '01.03.2008' and one with date '03.03.2008', I only want the row with date '03.03.2008' returned?

Thanks for any help!
Avatar of Sean Stuber
Sean Stuber

use datediff   to find the number of days between two dates.  and then use MIN to find the smallest difference

min(
datediff(d,date1,date2)
)
ASKER CERTIFIED SOLUTION
Avatar of Göran Andersson
Göran Andersson
Flag of Sweden image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kimfink

ASKER

Hi, thanks for the answers, the first question is now solved, it got like this:
WHERE ChemicalUsage.Stock.RegistrationDate BETWEEN @SpecifiedDate AND @SpecifiedDate + 1

I am still striving on my second question which I will elaborate.
I have a number of tanks which all have chemicals. I have three tables, tank. chemical and tankchemical. In the tankchmeical table i have tankId, chemicalId and registrationDate, so a tank could over a period of time have had several different chemicals on it.On a specified date I want to se the stock for all tanks and which chemical is on the tank, but I only want the chemical with registration date nearest the search date. The query I have now gives me all chemicals with registration date less than the search date, and I only want the nearest one....

DECLARE @SpecifiedDate datetime;
SET @SpecifiedDate = getutcdate()

SELECT ChemicalUsage.Tank.Id AS TankId, ChemicalUsage.Tank.TankName AS TankName, ChemicalUsage.Tank.TagId AS TagId, ChemicalUsage.Tank.ManualRegisteredTagId AS ManualRegisteredTagId, ChemicalUsage.Chemical.ChemicalName AS ChemicalName
FROM ChemicalUsage.TankChemical, ChemicalUsage.Tank, ChemicalUsage.Chemical
WHERE ChemicalUsage.TankChemical.RegistrationDate <= @SpecifiedDate AND
         ChemicalUsage.Tank.Id = ChemicalUsage.TankChemical.TankId AND
         ChemicalUsage.Chemical.Id = ChemicalUsage.TankChemical.ChemicalId