within current month sql... 1

[burnside]

this query should show all records where tbl_deal.dealdeldate is within current month

strsql = "SELECT tbl_location.locid, tbl_location.locname, tbl_deal.dealid, tbl_deal.userid, tbl_deal.dealdeldate, tbl_deal.dealdate, tbl_deal.stockid, tbl_deal.fincoid, tbl_deal.cusid, tbl_saledept.saledept, tbl_saledept.saledeptid, tbl_users.saledeptid, tbl_users.usersiteid,  tbl_salebar.barid, tbl_salebar.option, tbl_deal.saletypeid, tbl_salebar.imgsmlpath, tbl_stock.stockstateid, tbl_state.statedesc, tbl_deal.dealdeldate"

strsql = strsql & " FROM (tbl_location INNER JOIN tbl_users ON tbl_location.locid = tbl_users.usersiteid) INNER JOIN tbl_deal ON tbl_users.userid = tbl_deal.userid INNER JOIN tbl_salebar ON tbl_deal.saletypeid = tbl_salebar.barid INNER JOIN tbl_saledept ON tbl_users.saledeptid = tbl_saledept.saledeptid INNER JOIN tbl_stock ON tbl_stock.stockid = tbl_deal.stockid INNER JOIN tbl_state ON tbl_state.stateid = tbl_stock.stockstateid"

'sql current
strsql = strsql & " WHERE tbl_deal.dealdeldate = 100*Year(tbl_deal.dealdeldate)+Month(tbl_deal.dealdeldate) = 100*Year(curDate()) + Month(curDate())"

strsql = strsql & " ORDER BY tbl_deal.dealdate DESC"

but no records are found - if WHERE clause is taken out records are displayed
there is at least one record with dealdeldate 26/08/2005

 

[swampBoogie]

WHERE year(tbl_deal.dealdeldate) = Year(current_date)
and month(tbl_deal.dealdeldate) = month(current_date)

You would be better of creating strings (somewhat dynamically, preferably) containing the values

mStart = "2005-08-01 00:00:00"
mEnd = "2005-08-31 23:59:59"

...

strsql = strsql & _
" WHERE tbl_deal.dealdeldate between '" & _
" mStart & "' and '" & mEnd & "' "

in which case the DBMS could use any index on dealdeldate

 

[burnside]

any ideas why im getting this error

Error Type:
Microsoft VBScript compilation (0x800A0409)
Unterminated string constant
/dfasp2/dealgrouprep1.asp, line 51, column 70
strsql = strsql & " WHERE year(tbl_deal.dealdeldate) = Year(curDate())
---------------------------------------------------------------------^


on this line

strsql = strsql & " WHERE year(tbl_deal.dealdeldate) = year(curDate())and month(tbl_deal.dealdeldate) = month(curDate())"

is current_date the same as curdate

 

[burnside]

found whats wrong swampboogie - works great cheers

 

[swampBoogie]

is current_date the same as curdate

Yes, current_date is what is specified in the SQL standard though. CurDate() is specific to Mysql.