Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to name datafields AS

Author  Topic 

jarv
Posting Yak Master

131 Posts

Posted - 2015-01-28 : 07:37:23

The following SQL statement in my page works fine, I would like to use the part:
DATE_FORMAT(messages.created, '%d/%m/%Y') As created,
in my other SQL query
SELECT 
messages.id AS message_id, messages.order_id, messages.customer_id,
messages.subject,
messages.body,
DATE_FORMAT(messages.followup, '%d/%m/%Y') As followup,
DATE_FORMAT(messages.created, '%d/%m/%Y') As created,
DATE_FORMAT(messages.created,'%H:%i') AS created_time,
messages.created_by,
CONCAT_WS(' ',customers.firstname,customers.surname) AS customer,
customers.email AS email
FROM
messages INNER JOIN customers ON messages.customer_id = customers.id
WHERE
customers.consultant_id = '" . $consultant_id . "'
AND
messages.read = '" . $read . "'
AND
(messages.followup > 0 AND messages.followup <= '" . $timestamp . "')
ORDER BY
messages.id DESC LIMIT $start, $limit;



my other SQL query: I would like to add the following:
DATE_FORMAT(messages.created, '%d/%m/%Y') As created,

but I am not sure how to add this in?!


SELECT COUNT(*) as num FROM $tableName INNER JOIN customers ON messages.customer_id = customers.id
LEFT JOIN consultants ON consultants.id = customers.consultant_id
WHERE
messages.read = '" . $read . "'
AND
(messages.followup > 0 AND messages.followup <= '" . $timestamp . "')
AND
customers.consultant_id = '" . $consultant_id . "'
AND messages.created >= DATE_SUB(NOW(), INTERVAL 6 MONTH)

jarv
Posting Yak Master

131 Posts

Posted - 2015-01-28 : 07:40:59
so I'd basically liek to say something like this:


SELECT COUNT(*) as num,
DATE_FORMAT(messages.created, '%d/%m/%Y') As created,
FROM messages INNER JOIN customers ON messages.customer_id = customers.id
LEFT JOIN consultants ON consultants.id = customers.consultant_id
WHERE
messages.read = '1'

AND
customers.consultant_id = '340'
AND messages.created >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-01-28 : 08:45:45
This is SQL Server not MySQL forum.

We are the creators of our own reality!
Go to Top of Page

jarv
Posting Yak Master

131 Posts

Posted - 2015-01-28 : 10:09:22
are you not able to help me? this is SQL
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-01-28 : 10:41:15
I would like to help but this code is MySQL not TSQL although there are similarities they are different, and I have no way setup to test MySQL.

Look at the formats below and see how your DATE_FORMAT differs for example.

SELECT
messages.id AS message_id,messages.order_id,messages.customer_id,
messages.subject,
messages.body,
FORMAT (messages.followup, 'dd MM yyyy', 'en-US' ) AS followup,
FORMAT (messages.created, 'dd MM yyyy', 'en-US' ) AS created,
FORMAT (messages.created, 'hh:mm:ss', 'en-US' ) AS created_time,
messages.created_by,
CONCAT(' ',customers.firstname,customers.surname) AS customer,
customers.email AS email
FROM
messages INNER JOIN customers ON messages.customer_id = customers.id
WHERE
customers.consultant_id = '" . $consultant_id . "'
AND
[messages].[read] = '" . $read . "'
AND
(messages.followup > 0 AND messages.followup <= '" . $timestamp . "')
ORDER BY
messages.id DESC

We are the creators of our own reality!
Go to Top of Page

jarv
Posting Yak Master

131 Posts

Posted - 2015-01-29 : 06:24:16
I now get teh following error in my code:

#1052 - Column 'created' in where clause is ambiguous

SELECT COUNT(messages.id), messages.id as num, DATE_FORMAT(messages.followup, '%d/%m/%Y') As followup, DATE_FORMAT(messages.created, '%d/%m/%Y') AS created FROM messages INNER JOIN customers ON messages.customer_id = customers.id LEFT JOIN consultants ON consultants.id = customers.consultant_id WHERE messages.read IN (0,1) AND (messages.followup > 0 AND messages.followup <= '2015-01-29 11:19:39') AND customers.consultant_id = '127' AND created between '29/01/2015' and '01/01/1978'  AND messages.created >= DATE_SUB(NOW(), INTERVAL 6 MONTH) GROUP BY messages.id
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-29 : 07:07:36
use a two-part column name for the column created.
Go to Top of Page
   

- Advertisement -