Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

Aged Yak Warrior

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


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


SQL Slashing Gunting Master

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


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  
 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.02 seconds. Powered By: Snitz Forums 2000