CODEWITHSUNDEEP
javascriptmysqlnode.js
Ken Ingram
Ken Ingram

Reputation: 1598

Why is date_format(?,'%d-%b-%y') failing in node?

I'm using node to scrape some data and insert it into a table

The retrieved data is in the form ['03-Jan-14',20.18]

I have the SQL set up like this:

var sql = "INSERT INTO data (data_date, data_value) VALUES (date_format(?,\'%d-%b-%y\'),?)";
var insert_data = [parsedResults.data_date, parsedResults.data_value];
sql = mysql.format(sql, insert_data);

When I run the script I get this

INSERT INTO data (data_date, data_value) VALUES (date_format('03-Jan-14','NaN-%b-%y'), 20.18)

I've done some google searching and can't find a reason for this.

It seems as though JavaScript is interpreting %d rather than passing it as is.

Would love to get some insight on this problem.

Not looking for work-arounds, I want to know why this is failing in this particular way.

Upvotes: 2

Views: 1884

Answers (3)

Gamy Tuber
Gamy Tuber

Reputation: 1

db.query("SELECT id,Names,DATE_FORMAT(date_of_birth,'% %M %D %Y') FROM artists;"

This is the syntax you can use inside expressJS and Mysql

Upvotes: 0

rsiqueira
rsiqueira

Reputation: 21

Waking up the thread... To deal with %d in javascript strings, you must double it: '%%d-%b%y'.

Upvotes: 2

Ken Ingram
Ken Ingram

Reputation: 1598

The solution was to install the dateFormat npm module and format the date using that tool. Using MySQL str_to_date was then unnecessary.

I think this is probably much easier than having the SQL handle it.

I still have no idea how to prevent %d from being translated as a sprintf parameter.

Upvotes: 0

Related Questions