SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 date help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 07/18/2001 :  13:40:27  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
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  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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))



Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 07/18/2001 :  13:56:19  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000