Does MySQL REALLY not support date formatted @variables?
I am an experienced SQL Server developer working on a Mysql project. I just can’t believe that Mysql does not support @variables in date format!!! Although I see (bottom) what’s in the documentation, because of I’ve designed a query to hold the first date of activity for each customer in an @var, I’m still tempted to try:
set @mydate = STR_TO_DATE('Dec/15/2008', '%M/%d/%Y');
select @mydate;
but I get a value of BLOB, which I gather is Mysql’s way of punting.
So I ask, does the following REALLY mean no date formatted @vars? Why would MYSQL be designed this way?? Where is the code for @vars in the MYSQL source?:
from MYSQL doc: User defined variables can be assigned a value from a li开发者_如何学编程mited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value.
Late answer but drove me nuts! It returns Blob
in workbench but throws a collation error from the command line.
You need to cast the field as date - using date_format seems to mess with the data type.
E.g.
SET @StartDate := CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') AS Date); -- start of the month
maybe this will work (char seems to be less problematic)
set @mydate = cast( STR_TO_DATE('Dec/15/2008', '%M/%d/%Y') as char);
or to keep the original datetime
set @mydate = cast( STR_TO_DATE('Dec/15/2008', '%M/%d/%Y') as datetime);
精彩评论