Dates in SQL

[Kerri]

Hi,

I have a date 3/1/2004 (mm/dd/yyyy).

I have a DB table that contains dates before and after
this date.

I want to get the date that falls just before thsi date
from my table.

Currently I do a ...
SELECT TOP 1 MyDate
FROM MyTable
WHERE MyDate < 3/1/2004

This is incorrect. How do I bring bacj just the one record
whose date is less than this.

Note : I just want to bring back one record.


Thanks,
K.

 

[Rich Wallace]

SELECT TOP 1 MyDate
FROM MyTable
WHERE MyDate < '3/1/2004'
-- or --
SET ROWCOUNT 1
SELECT MyDate
FROM MyTable
WHERE MyDate < '3/2/2004'
SET ROWCOUNT 0

In the query, you're passing in a varchar value so you'll need to enclose
the date in single quotes.

HTH
-Rich

 

[Angel Saenz-Badillos[MS]]

select top 1 * from mytable where mydate < '3/6/2003' order by mydate desc

By ordering mydate in descending order you are guaranteed to get the date
that falls just before the date you re interested in. You may want to order
by primary key field as well to make sure your selects are deterministic(to
you) when you have multiple date fields with the same value.