Date comparison in MS SQL

[anders2002]

Hi,

I am trying to compare dates in MS SQL where the dates are stored in a 'Enddate' table in this format:

dd/mm/yyyy

I am trying to return records where the Enddate is less than 3 days away, and the less than 7 and more than 3.

Can anyone help?

I cannot get datediff to work?

I am currently using this type of syntax:

SELECT Generated_by, Customer, Contact, Transport, Reason, [Key], Expire, Startdate, convert(char,enddate,3) FROM LicenceDetails where (DATEDIFF(day, Enddate, GetDate()) <= 3) ORDER BY Enddate ASC

WHen I query the db in Enterprise Manager with this query:

SELECT DATEDIFF(day, Enddate, GETDATE()) AS datedifference
FROM LicenceDetails

It seems to return minus numbers as well which I don't want returned. Has anyone got any ideas?

Cheers,

Anders

 

[Sabnac]

try
SELECT Generated_by, Customer, Contact, Transport, Reason, [Key], Expire, Startdate, convert(char,enddate,3) FROM LicenceDetails where (DATEDIFF(day, GetDate(),Enddate) <= 3) ORDER BY Enddate ASC

 

[SQLBill]

What data type is ENDDATE? Is it datetime or varchar? If it's varchar, I don't believe you can use DATEDIFF without first converting ENDDATE to datetime.

-SQLBill

 

[anders2002]

Thanks for all your help with this query, the Enddate column was a datetime data type, and it was just the fact that i had the subtraction around the wrong way that I was getting strange results i.e. enddate - getdate, instead of getdate-enddate!!!!

Thanks again,

Anders