Str_To_Date Unknown Column Issue

[viviosoft]

Hello -

 

I'm trying to format a date (which works) howerver, I'm getting "Unknown column 'newDate' in 'where clause'"

 

Here's my sql statement:

 

SELECT *, STR_TO_DATE(invDetails.invoiceDate,'%m/%d/%y') AS newDate
	 FROM stores AS store
		 INNER JOIN invoice_details AS invDetails
		 ON store.sCustNum = invDetails.sCustNum
		 WHERE store.sSalesman = 508 AND store.BillToId = 7161
		 AND newDate BETWEEN '2012-01-01' AND '2012-12-31'
		 GROUP BY store.sCustNum

 

Here's the output WITHOUT the BETWEEN logic

 

(
   [id] => 1216
   [billToId] => 7161
   [sCustNum] => VS7121
   [sNumber] => 5263
   [sSalesman] => 508
   [sName] => CIRCLE K PICKERINGTON
   [sAddress] => 29 S HILL RD
   [sCity] => PICKERINGTON
   [sState] => OH
   [sZip] => 43147-1218
   [sPhone] => 6148379337
   [sFax] =>
   [sContact] =>
   [cId] => VS7121
   [invoiceNumber] => 097319
   [repNumber] => 512
   [invoiceDate] => 1/10/11
   [payMethod] => 2
   [invoiceType] =>
   [invoiceHistory] => 1
   [newDate] => 2011-01-10
)

 

Can't think of whatI might be doing wrong... thanks for any help you can provide me.

[Pikachu2000]

I'm pretty sure you can't use the field alias in the WHERE clause.

[viviosoft]

Thank you Pikachu2000... so I'm wondering what the alternative might be? I can't use HAVING because GROUP BY won't work. Do I need a nested SELECT? If so, what would the syntax look like? Thank you.

[viviosoft]

Correction... I can use GROUP BY, however I don't think this is the best approach as the Query takes almost 1 minute to complete. Any help would be great.

 

SELECT *, STR_TO_DATE(invDetails.invoiceDate,'%m/%d/%y') AS newDate
	 FROM stores AS store
		 INNER JOIN invoice_details AS invDetails
		 ON store.sCustNum = invDetails.sCustNum
		 GROUP BY store.sCustNum
		 HAVING store.sSalesman = 508 AND store.BillToId = 7161
		 AND newDate BETWEEN '2011-01-01' AND '2011-12-31'

[Pikachu2000]

Instead of the alias, try the longhand version of it in the WHERE.

 

SELECT *, STR_TO_DATE(invDetails.invoiceDate,'%m/%d/%y') as newDate
                FROM stores AS store
                        INNER JOIN invoice_details AS invDetails
                        ON store.sCustNum = invDetails.sCustNum
                        WHERE store.sSalesman = 508 AND store.BillToId = 7161
                        AND STR_TO_DATE(invDetails.invoiceDate,'%m/%d/%y') BETWEEN '2012-01-01' AND '2012-12-31'
                        GROUP BY store.sCustNum

[viviosoft]

Haha, I'm sorry.... duh. Thank you.

[PFMaBiSmAd]

Also, for the version using HAVING, you would only put the newDate condition in the HAVING clause. You would still use a WHERE clause for the other condition.

[DavidAM]

Store your dates as DATE or DATETIME not as VARCHAR. Then you can use the actual column in the WHERE clause instead of having to do the conversion again.