Select on date within certain interval

[Sashanan]

I'm trying to select records from an SQL Server 7.0 database which are after a given date and before another given date. All three dates are in the format dd-mm-yy. It should look something like this:

Select *
From Table1
Where Date(DateTarget) > Date(DateFirst) and Date (DateTarget) < Date(DateLast)

Except that Date() isn't a valid SQL function. Is there a function that does what I'm trying to do here, or do I need to make a complicated query with several uses of DatePart() to catch days, months and years individually?
&quot;Much that I bound, I could not free. Much that I freed returned to me.&quot;
(Lee Wilson Dodd)

 

[Cagliostro]

use where datediff(.yourdate,today) < yourInterval.
today is sql specific. In Access is date(), in SQLServer is GetDate(). The first parameter means years/days/month. So you put repective one of yy/y/mm/m/dd/d(also deppend on database). John Fill

ivfmd@mail.md

 

[Sashanan]

You put me on the right track and I'm pretty close to the solution now, but not quite there yet. According to my SQL manual, the Oracle solution would be:

SELECT *
FROM Table1
WHERE TargetDate BETWEEN to_date(DateFirst) AND to_date(DateLast)

to_date is Oracle-specific though, and my manual recommends using Datepart() as an alternative. Unfortunately, Datapart() seems to be intended only to get part of a date (like only the year, or only the month), and does not seem to have a switch to get the entire date the way to_date would.

Any idea how I can modify the query given above to make it work with SQL Server?
&quot;Much that I bound, I could not free. Much that I freed returned to me.&quot;
(Lee Wilson Dodd)

 

[Sashanan]

Got it working! By using the SQL Server function Convert instead of Oracle's to_date, I managed to achieve the same effect. Had some initial trouble with dates that refused to convert, but that turned out to be the result of faulty data (apparently caused by the millennium bug, unbelievable how that still haunted this database), and was easily fixed.

Thanks for your time.
&quot;Much that I bound, I could not free. Much that I freed returned to me.&quot;
(Lee Wilson Dodd)