|
|
I have a database with articles that expire after a certain date and this works fine. I would also like to make them expire at the date at a certain time.
Does anyone know how to do this?
Thanks in advance
Eddie
|
|
|
|
What database? What platform? What language? How do you get them to "expire" currently? What exactly do you mean by "expire"?
|
|
|
|
|
|
Hi , the language is ASP the database is Access or SQL server.
In the database i have the table articles.
this contains the following
ID (autonumber)
Title (text)
Article (memo)
Created (date) standard date format
Expiredate (datetime) shortdate 01-11-2004
Expiretime (datetime) shorttime 15:30
Using the date and time shown above the article should not display on the page as the date is now 05-11-2004
But I also wanted it not to display on the page at the date 01-11-2004 at 15:31
I hope this is info enough
Regards
Eddie_Eagle
|
|
|
|
Could you also give us the SELECT statement and/or Stored Procedure you're currently using?
<Added>
If you are responsible for the database design, you should combine the expire date and expire time fields into a single field of type "datetime". Then, your code that performs the query can compare to a single field.
If you can't change the database, you need to combine the two fields in your query, using the "add" operator, which is the plus sign "+", or even better, the DATEADD function.
I'd have to see your existing query/stored proc to show you a modified version.
|
|
|
|
|
|
Hi
Thanks
I have the code here:
Set rs = oConn.Execute("Select * from cases Where active <> 0 order by caseTitle")
Do While Not rs.Eof
ExpireDate=FormatDateTime(rs("expiredate"),vbShortDate)
DateNow=FormatDateTime(Date(), vbShortDate)
viewData=datediff("D",DateNow,ExpireDate)
If viewData >= 0 OR rs("expire") = 0 Then
' Display the data
Response.Write "<p>" rs("created") & "</p>"
Response.Write "<p>" rs("title") & "</p>"
Response.Write "<p>" rs("article") & "</p>"
rs.MoveNext ' go to next record
Loop
I have control of the database so I could change the expire date field.
Thanks in advance
regards
Eddie
|
|
|
|
Then that's definitely the way to go... store an entire timestamp, date plus time, in a single field in the database.
I would also modify your query. Add a where clause to only select non-expired records:
Set rs = oConn.Execute(@"Select * from cases Where (active <> 0
and ExpireDate > " +
DateNow=FormatDateTime(Date(), vbGeneralDate) +
") order by caseTitle")
|
|
Don't trust that code, I don't know what you need to do to make ASP return a complete datetime. In .NET it's "System.DateTime".
|
|
|
|
|
|
Hi
Thanks for your help
I changed the field expiredate to a general date in the database and changed the sql to this.
Set rs = oConn.Execute("Select * from cases Where (active <> 0 and ExpireDate > Now()) order by caseTitle")
This worked the way that I needed.
I have only tried this in access and wil try it in SQL server, but I know that SQL server treats date/time differnt than access so I am not sure it will work
Best regards
Eddie
|
|
|
eddieeagle
tgreer