Dynamic SQL Date Format String Percent Escape Mayhem
Hi guys
I have a 5.5.34 stored proc that is trying to issue a command with a date_format call in it. Its code looks like:
DECLARE stmt TEXT;
SET stmt = CONCAT('UPDATE ', mytable, ' SET isMondayFlag = LOWER(DATE_FORMAT(dt, \'%a\')) = \'mon\' ');
SET @stmtvar = stmt;
PREPARE prepstmt FROM @stmtvar;
EXECUTE prepstmt;
DEALLOCATE PREPARE prepstmt;
As you can see the ' is escaped normally but I cannot seem to figure out how to escape the % character. Ultimately, I believe what ends up happening is that the date_format pattern never matches the given pattern (desired %a for the 3 letter day of the week, Sun, Mon, Tue ..etc (lower function added in case I want to go case insensitive someday))
A lot of online docs point to the % as part of the LIKE context, which I believe is a different context than what I am using.
Any ideas?
Thanks!
Link:
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
Subject
Views
Written By
Posted
Dynamic SQL Date Format String Percent Escape Mayhem
2802
December 25, 2013 09:26PM
1034
December 25, 2013 10:15PM
945
December 26, 2013 01:07AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.