MySQL Forums
Forum List  »  Stored Procedures

Dynamic SQL Date Format String Percent Escape Mayhem
Posted by: curtis pastore
Date: December 25, 2013 09:26PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Dynamic SQL Date Format String Percent Escape Mayhem
2422
December 25, 2013 09:26PM


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.