how to name datafields AS - SQL Server Forums

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)

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!

jarv
Posting Yak Master

131 Posts

Posted - 2015-01-28 : 10:09:22

are you not able to help me? this is SQL

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!

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

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources