1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Date Range query problem

Discussion in 'C#' started by gwilson, Apr 11, 2006.

  1. #1
    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
    SEMrush
     
    gwilson, Apr 11, 2006 IP
    SEMrush
  2. tgo

    tgo Peon

    Messages:
    124
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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 :p


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

    Hope this helps at least a bit :)
     
    tgo, Apr 11, 2006 IP
  3. iShopHQ

    iShopHQ Peon

    Messages:
    644
    Likes Received:
    33
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Is the field ArticleAdded actually typed as a Date?

    Is it Access or SQL Server?
     
    iShopHQ, Apr 11, 2006 IP
  4. jaymcc

    jaymcc Peon

    Messages:
    139
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #4
    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
     
    jaymcc, Apr 11, 2006 IP
  5. vectorgraphx

    vectorgraphx Guest

    Messages:
    545
    Likes Received:
    16
    Best Answers:
    0
    Trophy Points:
    0
    #5
    GOD i hate dates. they're the WORST sort of nightmare field type to program around. bar none.
     
    vectorgraphx, Apr 11, 2006 IP