Date Range query problem

I'm having problems with a asp page / query which retrieves articles from a table where the date is less than or equal to now (ie. the date is a past date, or exactly now).

The code works 100% on my test computer (Windows XP), but as soon as I upload it to my webhost (Windows Server 2003) it fails to work correctly. It will also retrieve articles with a future date, which is incorrect. The idea is that articles can be entered with a future date and will only be retrieved once that date is reached or past, so articles can be queued, for example in a article series (multiple parts).

Code snippet is as follows:

strSQL = "SELECT ArticleID, ColumnID, ArticleTitle, ArticleSubTitle, ArticleSynopsis, ArticleText, ArticleReadCount, ArticleComments, ArticleTemplate, ArticleDateAdded, ArticleLive, AuthorAffirmsCopyright FROM TblArticles WHERE ArticleID=" & CStr(intThisArticle)
strSQL = strSQL & " AND (ArticleDateAdded<=#" & Now() & "#) AND (ArticleLive=True) AND (AuthorAffirmsCopyright=True)"
strSQL = strSQL & ";"

As mentioned, this works correctly on my computer, and returns an empty record set for the particular article I am testing this on. However, even though the article date is 11/05/2006 3:20:34 p.m. the article is retrieved on my webhost's server.

To make matters more frustrating, the following test change appears to work on the webhost's server:

strSQL = "SELECT ArticleID, ColumnID, ArticleTitle, ArticleSubTitle, ArticleSynopsis, ArticleText, ArticleReadCount, ArticleComments, ArticleTemplate, ArticleDateAdded, ArticleLive, AuthorAffirmsCopyright FROM TblArticles WHERE ArticleID=" & CStr(intThisArticle)
strSQL = strSQL & " AND (ArticleDateAdded>=#" & Now() & "#) AND (ArticleLive=True) AND (AuthorAffirmsCopyright=True)"
strSQL = strSQL & ";"

But how can "11/05/2006 3:20:34 p.m." be > "11/04/2006 11:55:00pm" and return an empty record set?

I made a test page and tried the following result:

response.write dArticleDateAdded & " - " & Now() & " : " & (dArticleDateAdded <= Now())

I get the following results:

test server: - 4/11/2006 11:57:31 PM : True
live server: 11/05/2006 15:20:34 - 11/04/2006 23:55:49 : False

As you can see test test server returns a null value for the dArticleDateAdded, due to an empty record set.

The "live" server returns a date, even though it is in the future. This is how I discovered that changing the (ArticleDateAdded<=#" & Now() & "#) to (ArticleDateAdded>=#" & Now() & "#) allows it to work on the webhost. But this makes no sense and is doing my head in!

Any suggestions would be greatly appreciated.

Kind Regards,
Graeme Wilson

 

What kind of DB system are you using? MSSQL? Access? MySQL?

The # delimiter should not be needed unless you are using Access. In SQL dates are read as strings i think. Try delimeting them with a ' and see what that does. - eg - http://www.aspfaq.com/show.asp?id=2023

Also I tend to not use NOW() with mysql. I use the built in getdate() function of MySQL. ASP and MySQL never return dates in the same format for me


good resourse for asp date info
http://www.aspfaq.com/search.asp?category=6

Hope this helps at least a bit

 

Is the field ArticleAdded actually typed as a Date?

Is it Access or SQL Server?

 

I think you have date format differences between your computer and the server. Look at the date formats 4/11 and then 11/4. Where is the server you are uploading to? If in EU it might be in euro format which is dd/mm/yyyy (and Australia for that matter where I am from). This trips me up quite a bit developing in AU when I am posting to a US server.

Regardless as a general rule I always work with dates in yyyy/mm/dd format remove date formatting issues. I would reformat the date accordingly instead of just pluggin in the now() function.

J

 

GOD i hate dates. they're the WORST sort of nightmare field type to program around. bar none.