Reputation: 313
Is there solution for '0000-00-00 00:00:00' problem, without changing table?
I have "[]" in this query:
dbh.select_all("select j.n, j.name, j.dsc, j.flag, j.td from job j where j.td='0000-00-00 00:00:00'")
I look solution for this: http://rubyforge.org/tracker/index.php?func=detail&aid=22243&group_id=234&atid=967
Upvotes: 0
Views: 1816
Reputation: 325
You can just cast the "0000-00-00 00:00:00" to NULL:
SELECT IF(mytime="0000-00-00 00:00:00",NULL,mytime) FROM mytable;
Upvotes: 0
Reputation: 18484
So your default is '0000-00-00 00:00:00' and you can't change it. I dug up my ruby-dbi mailing list archives and found the following explanation:
The problem with the latter case is that it can't be coerced to a DateTime object because it's not a valid time... Honestly, I'm surprised mysql allows it at all. Either way, you'll need to turn type conversion off (see
DBI.convert_types=
) to get this default value to work or change it and all occurrences of it in your database, or usebind_coltype
to treat it as string instead.
See this mailing list archive.
Upvotes: 2
Reputation: 1306
I usually declare my datetimes as accepting NULL, e.g.:
dtime DATETIME NULL DEFAULT NULL
That way I can check if a field IS NULL rather than if a field = '0000-00-00 00:00:00'.
Upvotes: 0