| Author |
 Topic  |
|
|
Onamuji
Aged Yak Warrior
USA
504 Posts |
 Posted - 07/18/2001 : 13:40:27
|
Ok, so dates are easy but I need to cut the time off...
there is a table
Files
-FileId
-Name
-Location
-StartDate
-EndDate
the start and end dates are what i'm having issues with... i need to just have them be dates but when they are inserted times get attached with them for some reason i have a stored procedure that retrieve all active documents which have a start date greater than or equal to today and an end date less than or equal to end date... i'm sure there is just a date function i've overlooked...
select FileId, Name, Location from Files where StartDate >= getDate() and EndDate <= getDate()
that doesn't work because of the time attribute on the column :-( any help, anyone?
Thanks :-D
Regards,
Adam |
|
|
AjarnMark
SQL Slashing Gunting Master
USA
3246 Posts |
Posted - 07/18/2001 : 13:54:03
|
Unfortunately, time is a part of life (at least so it seems in SQL 7). If you are inserting date-only data in these fields, then they should be showing up with 00:00:00 as the time. The way that I got around a similar problem was not to try to change the dates in my table, but to redefine my definition of "TODAY". You are using getdate() which also returns the current time, and will throw off your results. I have accomplished this, perhaps not the most efficient way, but by using
SET @Today = cast(month(getdate()) as char(2)) +'/'+ cast(day(getdate()) as char(2)) +'/'+ cast(year(getdate()) as char(4))
|
 |
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 07/18/2001 : 13:56:19
|
StartDate >= convert(varchar(11),getDate(),113) and
convert(varchar(11),EndDate,113) <= getDate()
or you could use datediff(dd,startdate,getdate()) >= 0 (or <= never remember which)
==========================================
Cursors are useful if you don't know sql. |
|
|
| |
Topic |
|
|
|
date help