StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL CAST 3 integers as DATE. Error or do not recognize date
text
Body
copied!<p>This is part of MySQL table</p> <pre><code> Amount | DebitAccount | RecordYear | RecordMonth | RecordDay </code></pre> <p>In MySQL query need <code>RecordYear,RecordMonth,RecordDay</code> change to <code>DATE</code>. </p> <p>Based on many advices get such working code (I show the code <strong>only as example</strong> to see how it works)</p> <pre><code>SELECT * FROM (SELECT * , CAST(CONCAT_WS('-', RecordYear,RecordMonth,RecordDay) AS DATE) RecordDate , CAST(CONCAT_WS('-', DocumentYear,DocumentMonth,DocumentDay) AS DATE) DocumentDate FROM 2_1_journal) DATA WHERE RecordDate <> ? AND DocumentDate = ? ORDER BY RecordDate DESC </code></pre> <p>This code selects all from table, changes/converts separated integers as <code>RecordDate</code> and gives me data based on <code>WHERE</code>.</p> <p>Here is second working code <strong>also only as example</strong></p> <pre><code>SELECT a.AccountNumber, IFNULL( d.Amount, 0 ) - IFNULL( c.Amount, 0 ) AS Amount FROM 18_7_ChartOfAccounts AS a LEFT JOIN ( SELECT DebitAccount, SUM( Amount ) AS Amount FROM 2_1_journal WHERE CAST(RecordDay AS UNSIGNED) != ? AND CAST(RecordMonth AS UNSIGNED) != ? AND CAST(RecordYear AS UNSIGNED) != ? GROUP BY DebitAccount ) d ON (a.AccountNumber = d.DebitAccount) </code></pre> <p>Need this <code>CAST(RecordDay AS UNSIGNED) != ? AND CAST(RecordMonth AS UNSIGNED) != ? AND CAST(RecordYear AS UNSIGNED) != ?</code> part of the second code </p> <p>to change to this <code>, CAST(CONCAT_WS('-', RecordYear,RecordMonth,RecordDay) AS DATE) RecordDate , CAST(CONCAT_WS('-', DocumentYear,DocumentMonth,DocumentDay) AS DATE) DocumentDate</code> part of the first code.</p> <p>I created such code</p> <pre><code>SELECT a.AccountNumber, IFNULL( d.Amount, 0 ) - IFNULL( c.Amount, 0 ) AS Amount FROM 18_7_ChartOfAccounts AS a LEFT JOIN ( SELECT DebitAccount, SUM( Amount ) AS Amount , CAST(CONCAT_WS('-', RecordYear,RecordMonth,RecordDay) AS DATE) RecordDate , CAST(CONCAT_WS('-', DocumentYear,DocumentMonth,DocumentDay) AS DATE) DocumentDate FROM 2_1_journal WHERE RecordDate = ? GROUP BY DebitAccount ) d ON (a.AccountNumber = d.DebitAccount) /*below code is for purpose to show whole code*/ LEFT JOIN ( SELECT CreditAccount, SUM( Amount ) AS Amount , CAST(CONCAT_WS('-', RecordYear,RecordMonth,RecordDay) AS DATE) RecordDate , CAST(CONCAT_WS('-', DocumentYear,DocumentMonth,DocumentDay) AS DATE) DocumentDate FROM 2_1_journal WHERE RecordDate = ? GROUP BY CreditAccount ) c ON (a.AccountNumber = c.CreditAccount) </code></pre> <p>In output get this error <code>SQLSTATE[42S22]: Column not found: 1054 Unknown column 'RecordDate' in 'where clause'</code></p> <p>As understand <code>CAST(CONCAT_WS('-', RecordYear,RecordMonth,RecordDay) AS DATE) RecordDate , CAST(CONCAT_WS('-', DocumentYear,DocumentMonth,DocumentDay) AS DATE) DocumentDate</code> must be located in other place. But what place? Possibly need to add <code>DATA</code> as in the first example.</p> <p>Code may look not understandable, but such is a code to get what is necessary. Please advice.</p> <p><strong>Update</strong></p> <p>Based on advice changed code</p> <pre><code>SELECT a.AccountNumber, IFNULL( d.Amount, 0 ) - IFNULL( c.Amount, 0 ) AS Amount FROM 18_7_ChartOfAccounts AS a LEFT JOIN ( SELECT * FROM ( SELECT DebitAccount, SUM( Amount ) AS Amount , CAST(CONCAT_WS('-', RecordYear,RecordMonth,RecordDay) AS DATE) RecordDate, CAST(CONCAT_WS('-', DocumentYear,DocumentMonth,DocumentDay) AS DATE) DocumentDate FROM 2_1_journal) DATA WHERE RecordDate = 2013-01-20 GROUP BY DebitAccount ) d ON (a.AccountNumber = d.DebitAccount) </code></pre> <p>I know that in table is row with date 2013-01-20. But in output result is 0 and no error.</p> <p>Possibly my negligence mistake in code....</p> <p>Made conclusion that code checks date only in the first row of MySQL table. If in the first row date match to user's input, code sums all values in column <code>Amount</code>. If does not match, result is 0.</p> <p><strong>Update 1</strong></p> <p>Seems finally this is working code. At the moment output is as expected. If possible, please, suggest improvements</p> <pre><code>SELECT a.AccountNumber, IFNULL( d.Amount, 0 ) - IFNULL( c.Amount, 0 ) AS Amount FROM 18_7_ChartOfAccounts AS a LEFT JOIN ( SELECT DebitAccount, SUM( Amount ) AS Amount FROM ( SELECT * , CAST(CONCAT_WS('-', RecordYear,RecordMonth,RecordDay) AS DATE) RecordDate , CAST(CONCAT_WS('-', DocumentYear,DocumentMonth,DocumentDay) AS DATE) DocumentDate FROM 2_1_journal) DATA WHERE RecordDate = ? GROUP BY DebitAccount ) d ON (a.AccountNumber = d.DebitAccount) LEFT JOIN ( SELECT CreditAccount, SUM( Amount ) AS Amount FROM ( SELECT * , CAST(CONCAT_WS('-', RecordYear,RecordMonth,RecordDay) AS DATE) RecordDate , CAST(CONCAT_WS('-', DocumentYear,DocumentMonth,DocumentDay) AS DATE) DocumentDate FROM 2_1_journal) DATA WHERE RecordDate = ? GROUP BY CreditAccount ) c ON (a.AccountNumber = c.CreditAccount) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload