#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Location
    Dublin
    Posts
    41
    Rep Power
    12

    working with Dates


    Dear All,

    I am having a lot of problems working with dates:-

    First of all I have a table called articles, and I have a date field, which is in text format, and I insert the dates for example 02/11/2003 in it. Now the user can enter articles that are bigger than todays date, however I want to display only those that are smaller than todays date. Is it possible to convert the text field I have for dates into numeric format? And if so, will the comparison work? At the moment it is not working since for the database, 11/02/2000 is bigger than 01/04/2003. Another thing, I wish to keep the Articles dates in text format for my purposes.

    My second problem is when coming to compare dates:-

    I have this sql statement
    sql = sql & " AND newsArticles.newsDate BETWEEN " & "'" & FromDate & "'" & "AND '" & ToDate & "'"

    but it is not working properly. This is the result when I write the sql with a response.write:-

    select newsArticles.newsID, newsArticles.newsMainSecID, newsArticles.categoryID, newsArticles.newsDay, newsArticles.newsMonth, newsArticles.newsYear,newsArticles.newsDate, newsArticles.newsTitle, newsArticles.newsAuthor, newsArticles.newsSummary, newsArticles.newsText,newsCategory.categoryName, newsMainSec.newsMainSecName from (newsArticles INNER JOIN newsCategory ON newsArticles.categoryID = newsCategory.categoryID) INNER JOIN newsMainSec ON newsArticles.newsMainSecID = newsMainSec.newsMainSecID WHERE newsArticles.newsMainSecID = 1 AND newsArticles.newsDate BETWEEN '01/01/2000'AND '01/04/2002' ORDER BY newsArticles.newsDate Desc

    and it is not returning any results when there should be. What am I doing wrong?

    Your help is very much appreciated!
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2001
    Posts
    9
    Rep Power
    0
    Why don't you just use a date field of the DATE type? Ms Access has a Date/Time field and (My)SQL has a date field of the YYYY-MM-DD format.
    All you have to do (once you have a proper date field) is convert your date to the current format.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    138
    Rep Power
    12
    Just to state obvious you could use datediff or dateadd functions
    --

    ngibsonau

IMN logo majestic logo threadwatch logo seochat tools logo