How Do I Pass Yesterday Date Variable into MySQL? 1

[3dColor]

Below returns zero recordset from the stats table (when I know there are 100's):

<cfset today = createDate(year(now()), month(now()), day(now()))>
<cfset yesterday = dateAdd("d", -1, today)>

<cfquery name="GetAptsStats" datasource="#DSN#">
   	SELECT		listingFk, listingId, timeStampStats
   	FROM 		stats INNER JOIN listing ON stats.listingFk = listing.listingId
	WHERE 		proptype = 4
				AND timeStampStats = #yesterday# 
</cfquery>

Below returns all the proptype=4 regardless of the date:

<cfset today = CreateODBCDate(Now())>
<cfset yesterday = CreateODBCDate(DateAdd('d', -1, #today#))>

<cfquery name="GetAptsStats" datasource="#DSN#">
   	SELECT		listingFk, listingId, timeStampStats
   	FROM 		stats INNER JOIN listing ON stats.listingFk = listing.listingId
	WHERE 		proptype = 4
				AND DATE(timeStampStats) = #yesterday# 
</cfquery>

This gives me a "Unknown column 'theDate' in 'where clause'" error:

<cfset today = CreateODBCDate(Now())>
<cfset yesterday = CreateODBCDate(DateAdd('d', -1, #today#))>

<cfquery name="GetAptsStats" datasource="#DSN#">
   	SELECT		listingFk, listingId, DATE(timeStampStats) AS theDate
   	FROM 		stats INNER JOIN listing ON stats.listingFk = listing.listingId
	WHERE 		proptype = 4
				AND theDate = #yesterday# 
</cfquery>

How do I pass the yesterday data variable into MySQL?

Thanks,
Dave

 

[towerbase]

SELECT DATE(DATE_SUB( NOW(), INTERVAL 1 DAY))

will give you yesterday's date.

If you want the time as well as the date then remove the DATE function call:

SELECT DATE_SUB( NOW(), INTERVAL 1 DAY)

Andrew
Hampshire, UK

 

[3dColor]

Andrew, thanks for answering my question.

I tried this but it returns all the records instead of the ones just for yesterday. What did I do wrong?

<cfquery name="GetAptsStats" datasource="#DSN#">
   	SELECT		listingFk, listingId, 
    			DATE(DATE_SUB( timeStampStats, INTERVAL 1 DAY))
   	FROM 		stats INNER JOIN listing ON stats.listingFk = listing.listingId
	WHERE 		proptype = 4
</cfquery>

 

[towerbase]

You've asked it to return records where

protype = 4

Looking at your original post I would have thought that something like

WHERE protype = 4 AND theDate = DATE(DATE_SUB( timeStampStats, INTERVAL 1 DAY))

would be appropriate.

It's usually a good idea to get the SQL working in something like MySQL Query Browser or HeidiSQL before worrying about the <cfquery ...> tags. After all this is a MySQL forum and actually I don't understand those tags ....

Andrew
Hampshire, UK

 

[r937]

what you will want to do is use a range test for the timeStampStats column, as that's the only way to pull out the range of rows you want efficiently

<cfset today = CreateDate(Year(Now()),Month(Now()),Day(Now()))>
<cfset yesterday = DateAdd('d',-1,#today#)>
<cfquery name="GetAptsStats" datasource="#DSN#">
SELECT listing.listingId
, listing.othercolumns
FROM stats
INNER
JOIN listing
ON listing.listingId = stats.listingFk
AND listing.proptype = 4
WHERE timeStampStats >= '#yesterday#'
AND timeStampStats < '#today#'
</cfquery>

see how that works?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[3dColor]

Thanks Rudy that worked.