date field

adamhw · July 21st, 2012, 04:25 AM

Hi there

I've got 2 x sql statements - the first one I want to pull out records between certain date ranges, the second I want to pull out records after their end date has passed.

This is what I've got set up for pulling out the records between certain date ranges:

SELECT * FROM tbl_tenders t WHERE t.startdate <= 21/07/2012 AND 21/07/2012 <= t.enddate AND IsLive = 0 ORDER BY t.startdate DESC

For the records whose enddate have passed I have:

SELECT * FROM tbl_tenders t WHERE 21/7/2012 > t.enddate

the enddate field I have in the database is a datetime field written such as: 2012-07-19 00:00:00.000 though not sure this should make any difference

What's happening is that all the records are being shown using the first sql statement regardless of the date

can any one advise?

thanks

Adam

mat41 · July 22nd, 2012, 06:37 PM

dates need to be strings they so they needs quotes. here is what a typical SQL statement that gets data between two dates looks like:

SELECT filedName FROM tbleName WHERE (dateTypeFieldName >= '7/21/2012') AND (dateTypeFieldName <= '8/22/2012')

Yours should be giving you an error, does it run like that....

Notice my is mm/dd/yyyy - We use dd/mm/yyyy here is aussie but I always query SQL Server in American format. using this function

Code:

  FUNCTION amDate(varDate)
      IF isNull(varDate) OR Trim(varDate) = "" OR varDate = "Null" THEN
        amDate = "Null"
      ELSE
        amDate = "'" & Day(DateValue(varDate)) & "/" & Month(DateValue(varDate)) & "/" & Year(DateValue(varDate)) & " " & TimeValue(varDate) & "'"
      END IF 
  END FUNCTION

so when you write your SQL do this:

Code:

sql = "SELECT filedName FROM tbleName WHERE (dateTypeFieldName >= " & amDate(request.form("dateFrom")) & ") AND (dateTypeFieldName <= " & amDate(request.form("dateTo")) & ")

NOTE - the amDate function placed single quotes arounf the value this is why I do this:

" & amDate(request.form("dateFrom")) & "

instead of this:

'" & amDate(request.form("dateFrom")) & "'

adamhw · July 23rd, 2012, 04:23 AM

thanks for your reply Matt - all sorted now.

sorry I posted twice, not sure which category it fell under.

thanks

Adam