April 1st, 2003, 03:14 AM
working with Dates
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!
April 7th, 2003, 07:51 AM
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.
April 12th, 2003, 03:07 AM
Just to state obvious you could use datediff or dateadd functions