DATE value being returned as a FLOAT or something. Can someone help ??

[mOrloff]

I'm connecting to the DB with LibreOffice Base, and initially forgot that this isn't a MySQL db (it's our only non-mysql db), so I have to say thanks to those from the other forum who helped get me started when I mis-posted.

Anyhow, here's what I've got:

SELECT DISTINCT c.primaryemailaddress, c.companyname, CAST(m.maxdate AS DATETIME ) AS maxDateCreated
FROM companycontact AS c, 
   (SELECT so.companyname, MAX(so.date_created) AS maxdate
    FROM ordhed_sales AS so, company AS co
    WHERE so.companyname = co.companyname
    AND co.isOEM = '1'
    GROUP BY so.companyname) AS m
WHERE c.companyname = m.companyname
AND c.primaryemailaddress IS NOT NULL
AND c.primaryemailaddress NOT LIKE ''
AND c.primaryemailaddress NOT LIKE ' '
ORDER BY m.maxdate DESC

When I run the sub-query by itself, the date_created value gets returned as expected ... as a date value.
However, when I run it as shown above, the values get returned like 40751.31, or 40750.6, or 40750.52, etc....

What do I need to do in order to have it returned in a date value??

[finito]

Try Running this separately

SELECT DISTINCT c.primaryemailaddress, c.companyname, CAST(m.maxdate AS DATETIME ) AS maxDateCreated
FROM companycontact AS c

Then try this separately.

SELECT so.companyname, MAX(so.date_created) AS maxdate
FROM ordhed_sales AS so, company AS co
WHERE so.companyname = co.companyname
AND co.isOEM = '1'
GROUP BY so.companyname) AS m

Peace.

[mOrloff]

Thanks-a-BUNCH for the reply.
Both of those returned errors (because calling for an unknown value and a slight syntax problem, respectively, The sub-query still had the ) AS m on the tail end).

I did re-test the subquery by itself after fixing that syntax error just for thoroughness sake, and as expected, it still runs fine.

Now, just to see what would happen, I also tried casting the date directly in the sub-query and running it by itself (separately)...

SELECT so.companyname, CAST(MAX(so.date_created)AS DATETIME) AS maxdate
FROM ordhed_sales AS so, company AS co
WHERE so.companyname = co.companyname
AND co.isOEM = '1'
GROUP BY so.companyname

...and it returned something peculiar.
The date values are getting returned as those FLOAT-ish values (40490.58, 40366.51, etc). The same problem as before.

Hmmmmmm ... so I tried switching the CAST from DATETIME to DATE, and viola, date values.

Soooo ... I plugged the same solution into the main query (the one in my OP), and BINGO!, we're running (after changing the order by to ORDER BY maxDateCreated).

AWESOME!! We found the solution!
Thanks-a-bunch (again) :)

But ... why?
Can someone explain why that was happening?
I'd like to learn all I can, and understanding the root of my problem would help tons.

~ Mo