Data Truncation: Truncated Incorrect DOUBLE Value When Updating Timestamp

Posted February 18, 2009 at 3:04 PM by Ben Nadel

Tags: ColdFusion, SQL

Reader Comments

Good thing that you have your own company. If you worked for someone they might have ripped you for "wasting" an hour.

Those bugs are so much fun.

@Brandon,

Hey, I was still doing client work :)

Just curious. Why did you write out:
c.date_ended = <cfqueryparam value="#Now()#" cfsqltype="cf_sql_timestamp" />
When MySQL has a built in "now()" function. Would it be cleaner (and a mirco-fraction faster) if you did this:
c.date_ended = now()

I imagine you have your reasons.
(Like:
To keep the SQL cross DB compliant
DB server isn't in same timezone as Webserver
)

@Tim,

I just use it out of habit. I like to keep the SQL as consistent as possible and so I use CFQueryParam for ally my dynamic value. Plus, sometimes I have dates that have to be null based on other values and CFQueryParam makes that wicked easy:

<cfqueryparam value="#FORM.date_started#" type="cf_sql_timestamp" null="#(NOT IsDate( FORM.date_started ))#" />

With this, I can put my null-ify logic right into the CFQueryParam tag and keep my SQL free of CFIF statements regarding NULL inserts.

WOW. I just did the same thing! I actually spent a couple hours trying to figure this out :(

@Chuck,

Yeah, the error message is SOOOO misleading!

Whahaha. You just saved me an hour ;)

@Jrgns,

No problem my man :) It's a horribly unhelpful error message, so glad to help.

GOD!

same mistake here.

the error message REALLY HELPED(in making debugging more complex I mean:P)

Thanks for that one Ben :)

Wow, you just saved me an hour. Thanks.

Thanks man, saved me an hour for sure...will remember this one for a long time.

Thanks very much!Actually you saved me an hour.and thanks you one hour.

Glad to help out guys.

Just saved me a morning of frustration too!

Thanks!

MySQL raised same crappy error on the following syntax:

select IFNULL(id,0)+1 into nextval from nextids where UPPER(tablename)=UPPER('''' + tbname + '''')

The correct syntax should be:

select IFNULL(id,0)+1 into nextval from nextids where UPPER(tablename)=UPPER(CONCAT('''',tbname,''''));

Thank you,Ben. It is your post that enlightened me to stop searching for errors in places where there aren't chances to find one :))

@Lucian,

Going from MS SQL to MySQL, it took me a while to get in the mindset of CONCAT(). In MS SQL, using the "+" is so easy.

You wasted an hour of your life but saved me one - thanks for posting this!

@Ben

Yes, Ben, couldn't they just have used that tiny "+" sign? :))

Oh, another thing!
After using the concat, i kept receiving this error: "No data fetched and-some-other-thing-bla-bla" Said to myself: "Bugger! What could be wrong this time?"

Well, since I am a Delphi developer, at my origins, i always add the quotes when comparing a constant-value string to a variable string/a string-type parameter.
Well (again), it seems that MySQL doesn't need the quotes, it has it's own. Very ashamed of not knowing this, stop "boo"-ing me.

So, finally, the winning syntax was:

select IFNULL(id,0)+1 into nextval from nextids where UPPER(tablename)=UPPER(tbname)

It was right there, in front of me, all the time :)

Nice night to you, Ben, and thanks again.

Lucian

@Lucian,

Sometimes, error just seem to do more harm than good :) They almost act as misdirection.

Saved me the hour! Thanks!

@JGreen,

My pleasure!

Similar issue: appear to have a problem with a double, but that is not the cause....

create table test (col1 varchar(10), col2 varchar(10), col3 double);

insert into test values ("a","b",0);
update test set col3=1.2 where col1+col2="a"+"b";

Query OK, 1 row affected, 2 warnings (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

show warnings;
+---------+------+---------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'b' |
+---------+------+---------------------------------------+

update test set col3=1.2 where concat(col1,col2)="a"+"b";

Query OK, 0 rows affected, 2 warnings (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

To work without warnings,

update test set col3=1.2 where col1+col2=concat("a","b");

or

update test set col3=1.2 where concat(col1,col2)=concat("a","b");

@Adam,

Not that this is related to the problem at all, but don't you need single quotes when dealing with SQL string values?

@Ben:

No, double quotes work as well. He just has to pay attention to the letter case.

Greetings,
Lucian

@Lucian,

Really? I had no idea. Is this true for all SQL? Or just some databases?

@Ben
Weeeeelll ... it did apply to MySQL and SQL Server and Firebird.

Can't be sure about Interbase, though, it's been a long time.

Lucian

@Lucian,

Very cool; I'll have to give this a try.

@Ben

Keep me posted. Who knows? Some things might have changed.

Lucian

Same error in 5.0.67 :(
oh well thanks for saving my debugging time.

Hey mate,

Thanks to Google and your blog I now know why I have been getting this error for the past 3 months. Now I don't have to update fields one by one like a sucker.

Nate
Adelaide, Australia

@Nate,

Awesome my man. Glad this was able to help you sort out the problems.

thanks!

Ben, you just saved me a lot of time. Thanks!

Nice work, Ben. I can really count on you. Seriously.

@Ari, @Hrish, @Mike,

Always happy to help. Glad this post helped you narrow down your problem.

Thanks a loads man!!
hw silly on my part

you rock!!

Commas, not ANDs in MySQL UPDATE statements. Just what I needed - thanks for posting those comments - you rock.

Awesome, saved my time.

Small but very typical syntax error.
U saved my hour.
Thanks.

Well FWIW you saved ME an hour tonight.

THANKS!

Thaks for your hour man!!!!

Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment