With a bit of experimenting...
SELECT CAST(DATE_FORMAT((MessageDate + INTERVAL 9 HOUR),'%d/%m/%Y') AS
CHAR) AS Purchase_Date,
sum(ItemCount) AS TotalSales
FROM table
Works for my purposes this time, but it would still be nice to know why the
group by clause caused MySQL to convert the Date column to Binary data if
any one knows.
--
Jay
> -----Original Message-----
> From: James Smith [mailto:[EMAIL PROTECTED]
> Sent: 03 September 2004 12:58
> To: CF-Talk
> Subject: RE: Awkward SQL
>
> Ok, extension of this problem....
>
> SELECT CAST((MessageDate + INTERVAL 9 HOUR) AS DATE) AS
> Purchase_Date, ItemCount
> FROM table
>
> Works just fine and returns the dates exactly as I want,
> unfortunately I need to be using sum(ItemCount) giving me...
>
> SELECT CAST((MessageDate + INTERVAL 9 HOUR) AS DATE) AS
> Purchase_Date,
> sum(ItemCount) AS TotalItems
> FROM table
> GROUP BY Purchase_Date
>
> And unfortunately as soon as I group on the date it gets
> mutated into binary data instead of the date.
>
> Any ideas on why and how to get round it?
>
> --
> Jay
>
>
>
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
- Re: Awkward SQL Paul Hastings
- RE: Awkward SQL James Smith
- RE: Awkward SQL James Smith
- Re: Awkward SQL Jochem van Dieten
- RE: Awkward SQL James Smith
- Re: Awkward SQL Jochem van Dieten
- Re: Awkward SQL Jochem van Dieten
- Re: Awkward SQL Claude Schneegans
- RE: Awkward SQL James Smith
- RE: Awkward SQL James Smith
- James Smith