Want a date but getting a blob

[SBpsc]

Hi:

I am writing a query using MySQL and am running into some problems. Hopefully someone can help. My first query gets the year and month from the database and then concats them to create a datetime. When I run that query by itself, it displays the correct datetime format. However, when I union that query to another query which has real datetimes and then run the query, it does not show me a datetime anymore, rather it comes up as a blob. What am I doing wrong?

SELECT
   tb1.portal_id,
   CONCAT(tb12.TheYear,IF (tb11.TheMonth<10,'-0','-'),tb11.TheMonth,'-01 00:00:00') AS KeyDateTime
   ...
FROM
   portals AS tb1
...

UNION ALL

SELECT
   tb1.portal_id,
   tb2.StartDateTime,
   ...
FROM
   portals AS tb1
...

PS: The Month is retrieved from a column in the database which is INT unsigned. The Year is retrieved from a column in the database which is varchar(255).

Thanks in advance.

 

[KarveR]

Well, thats a really intersting way you have of getting and storing dates, whatever is wrong with a field of type DATE ?

as for the query, I'll pass there's not really enough of it to make any sense(ible suggestions)

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[TonyGroves]

The field types in the union are determined by the field types in the first select. If you swap around the selects, that should work.

 

[SBpsc]

Thanks Tony, but that resulted in a blob too.
I did get it work by using the following:

FROM_DAYS(TO_DAYS(CONCAT(tb12.TheYear,IF (tb11.TheMonth<10,'-0','-'),tb11.TheMonth,'-01 00:00:00')))

...ugly, but its get the job done.

Thanks again,
SB

 

[TonyGroves]

You could instead wrap the concat in a cast:[tt]
CAST(... as DATETIME)
[/tt]