# Function node - getFullYear error

**URL:** https://discourse.nodered.org/t/function-node-getfullyear-error/42926
**Category:** General
**Created:** [20 March 2021 13:16 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926 "2021-03-20T13:16:36Z")
**Posts on this page:** 20
**Page:** 1

<div class="post-metadata">

### Author: ![rakgupta](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rakgupta/32/42535_2.png) [@rakgupta](https://discourse.nodered.org/u/rakgupta)
#### Post date: [20 March 2021 13:16 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/1 "2021-03-20T13:16:36Z")

</div>

Hello:

I have a flow that selects data from a table and inserts the data into a backup table in another database, This has been working just fine for over 6 months. After I select the data from the source, a function node converts the data into a SQL statement to insert into the target, Today I started getting an error in the function "getFullYear" where I am parsing datetime data from the source and building a date string. The error that I get is "m.created.getFullYear() is not a function".

The code in the function node is:

```auto
var m = msg.payload
var t =""

for(x=0;x<m.length;x++){
    t = "INSERT INTO events SET row_id="+m[x].row_id+", source='"+m[x].source+"',name =' "+m[x].name
    t=t+ "', displayName='"+m[x].displayName+"', value='"+m[x].value+"', unit='"+m[x].unit+"', deviceId="+m[x].deviceId
    t = t+", hubId="+m[x].hubId+", locationId="+m[x].locationId+", installedAppId="+m[x].installedAppId
    t= t+ ", descriptionText='"+m[x].descriptionText+"', created="
    var year=m[x].created.getFullYear() **<= Error here**
    var month = m[x].created.getMonth()+1
    var date = m[x].created.getDate()
    var hour = m[x].created.getHours()
    var minute = m[x].created.getMinutes()
    var seconds = m[x].created.getSeconds()
    var sqlDate = year+"-"+month+"-"+date+" "+hour+":"+minute+":"+seconds
    t=t+"STR_TO_DATE('"+sqlDate+"','%Y-%m-%d %H:%i:%s')"
    
    node.send({topic:t})
    //x=(m.length)+1
}

```

Any idea what is going on? NR version is 1.2.7 on RPi4. Thanks for your help.

---

<div class="post-metadata">

### Author: ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [20 March 2021 13:28 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/2 "2021-03-20T13:28:17Z")

</div>

It is most likely that msg.payload.[...].created is not a JavaScript date object - almost certain in fact since you pulled the data from another db.

You need to look at the format of that date/time string (or maybe number) to make sure that it can be parsed to a JavaScript Date.

---

<div class="post-metadata">

### Author: ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [20 March 2021 13:42 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/3 "2021-03-20T13:42:20Z")

</div>

> [@rakgupta](#):
>
> This has been working just fine for over 6 months.

Have you updated any contrib nodes or node-red or nodejs or the database table structure?  
Also, what SQL DB (e.g. mySQL, MSSQL, sqllite?) and what SQL contrib node are you using?

#### On you fixing what you have

To understand what is happening, add the following on the first line after the `for(...) {`

```javascript
node.warn(`type of created: ${typeof m[x].created}, value of created: ${m[x].created}`);

```

What type and what value to you see?

---

<div class="post-metadata">

### Author: ![rakgupta](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rakgupta/32/42535_2.png) [@rakgupta](https://discourse.nodered.org/u/rakgupta)
#### Post date: [20 March 2021 13:47 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/4 "2021-03-20T13:47:50Z")

</div>

> [@Steve-Mcl](#):
>
> Have you updated any contrib nodes or node-red or nodejs or the database table structure?

I have not updated node-red or node.js recently (at least since last week when this was working). Also, I have not changed the table structure of this particular table.

> [@Steve-Mcl](#):
>
> Also, what SQL DB (e.g. mySQL, MSSQL, sqllite?) and what SQL contrib node are you using?

I'm using mySQL and node-red-node-mysql (v 0.1.4). I did update this node from v0.1.2 yesterday ☹

---

<div class="post-metadata">

### Author: ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [20 March 2021 13:52 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/5 "2021-03-20T13:52:39Z")

</div>

> [@rakgupta](#):
>
> I did update this node from v0.1.2 yesterday ☹

I think we have a winner 😉

If you are able to test, please re-install the v0.1.2 and restart node-red. Does it fix your issue?

To install older version...

- cd into your node-red folder (usually `cd ~/.node-red` or `cd c:\users\USERNAME\.node-red`
- `npm install node-red-node-mysql@0.1.2`

---

<div class="post-metadata">

### Author: ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [20 March 2021 13:53 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/6 "2021-03-20T13:53:48Z")

</div>

Don't forget to restart node-red after doing a command line install.

---

<div class="post-metadata">

### Author: ![rakgupta](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rakgupta/32/42535_2.png) [@rakgupta](https://discourse.nodered.org/u/rakgupta)
#### Post date: [20 March 2021 13:56 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/7 "2021-03-20T13:56:23Z")

</div>

> [@Steve-Mcl](#):
>
> To understand what is happening, add the following on the first line after the `for(...) {`

Here is what I get:

type of created: string, value of created: 2021-03-13T10:30:00.000Z

I tried converting this to java date/time but still get an error on .getFullYear

var dateJS = new date(m.created)  
var dateYear = dateJS.getFullYear()

> [@Steve-Mcl](#):
>
> please re-install the v0.1.2

Ugh - I use a pre-bulit image of Homebridge so will have to make sure that I don't mess that up!! Will try that.

---

<div class="post-metadata">

### Author: ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [20 March 2021 13:57 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/8 "2021-03-20T13:57:03Z")

</div>

you dont have to - you can work around this.

```auto
var dateJS = new Date(m[x].created)
var dateYear = dateJS.getFullYear()

```

YOUR ERROR: it is `new Date` - NOT - `new date` (case sensitive)

---

<div class="post-metadata">

### Author: ![rakgupta](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rakgupta/32/42535_2.png) [@rakgupta](https://discourse.nodered.org/u/rakgupta)
#### Post date: [20 March 2021 14:14 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/9 "2021-03-20T14:14:43Z")

</div>

> [@Steve-Mcl](#):
>
> YOUR ERROR: it is `new Date` - NOT - `new date` (case sensitive)

THANK YOU! That seems to work. Do you think I should create an issue on Github for this or would change be by design?

---

<div class="post-metadata">

### Author: ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [20 March 2021 15:37 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/10 "2021-03-20T15:37:06Z")

</div>

A minor release would not intentionally introduce a breaking change, so yes, I think you should create an issue.

---

<div class="post-metadata">

### Author: ![rakgupta](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rakgupta/32/42535_2.png) [@rakgupta](https://discourse.nodered.org/u/rakgupta)
#### Post date: [20 March 2021 21:57 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/11 "2021-03-20T21:57:45Z")

</div>

@colin @Steve-Mcl

Very strange - I reverted to 0.1.2 on a test instance of NR and I still get the same error. I did restart NR and confirmed that I the version of node-red-node-mysql is 0.1.2. I was going to create and issue but now I'm not sure what is causing this.

---

<div class="post-metadata">

### Author: ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [20 March 2021 22:18 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/12 "2021-03-20T22:18:44Z")

</div>

What exactly is it that has changed?

---

<div class="post-metadata">

### Author: ![rakgupta](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rakgupta/32/42535_2.png) [@rakgupta](https://discourse.nodered.org/u/rakgupta)
#### Post date: [20 March 2021 22:34 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/13 "2021-03-20T22:34:04Z")

</div>

I'm trying to remember - but the basic platform has not changed. I updated the mySQL node and some other nodes that were showing updates (unfortunately I can't recall which ones).

---

<div class="post-metadata">

### Author: ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [21 March 2021 07:16 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/14 "2021-03-21T07:16:48Z")

</div>

I meant what is it that the SQL node does that it is now different? The code you posted suggested that it used to return a Date object, but now it returns a String.

---

<div class="post-metadata">

### Author: ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)
#### Post date: [21 March 2021 10:05 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/15 "2021-03-21T10:05:36Z")

</div>

I think this comes from a naive fix from this thread - [Splitting array - from MySQL query](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/)  
I have now tried to do a better object copy in version 0.1.5 so hopefully anything is now preserved, though my simplistic testing always seems to return strings anyway.

---

<div class="post-metadata">

### Author: ![rakgupta](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rakgupta/32/42535_2.png) [@rakgupta](https://discourse.nodered.org/u/rakgupta)
#### Post date: [21 March 2021 11:00 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/16 "2021-03-21T11:00:36Z")

</div>

> [@Colin](#):
>
> The code you posted suggested that it used to return a Date object, but now it returns a String.

Yes - that's what I was hoping to see when I reverted to 0.1.2 but it is typed as "string" now. I put in a debug to get the type and this is what is shows:

`type of created: string, value of created: 2020-06-01T10:15:00.000Z`

> [@Colin](#):
>
> I meant what is it that the SQL node does that it is now different?

I would agree that it seemed to be returning a date type before (or at least the function node was able to work with it like a date object), but now I can't get it to work even with 0.1.2

---

<div class="post-metadata">

### Author: ![rakgupta](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rakgupta/32/42535_2.png) [@rakgupta](https://discourse.nodered.org/u/rakgupta)
#### Post date: [21 March 2021 11:14 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/17 "2021-03-21T11:14:52Z")

</div>

> [@dceejay](#):
>
> I have now tried to do a better object copy in version 0.1.5 so hopefully anything is now preserved, though my simplistic testing always seems to return strings anyway.

Unfortunately, it's not working with 0.1.5 either. My table structure is:

 ![Screen Shot 2021-03-21 at 6.05.19 AM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/0/b0fb359569bdd9556f5f6a9c04e3d4534f87112d.png)

The code in the function node that I'm using to debug is:

 ![Screen Shot 2021-03-21 at 6.08.26 AM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/9/5900744f8ce47c1a7a670453999fc48da68b2b35.png)

Similar code was working last week as I have a scheduled flow that copies data from one source to a backup and it stopped working yesterday. I have worked around this by converting the date returned from the query into a java date (using new Date()). Please let me know if you need any other information. Thanks.

EDIT: This seems that node.js was returning dates as javascript dates. Could something have changed there? (see response # 16).  
[https://stackoverflow.com/questions/32100434/mysql-returns-full-datetime-string-on-select-query-when-column-type-is-date](https://stackoverflow.com/questions/32100434/mysql-returns-full-datetime-string-on-select-query-when-column-type-is-date)

---

<div class="post-metadata">

### Author: ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [21 March 2021 11:45 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/18 "2021-03-21T11:45:00Z")

</div>

In particular from that post  
"this feature can be turned off by setting `dateStrings` to `true` in the connections:"

So that suggests that maybe something has changed and that flag is now set to true where it was set to false (or maybe defaulting to false) previously.

---

<div class="post-metadata">

### Author: ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [21 March 2021 11:59 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/19 "2021-03-21T11:59:42Z")

</div>

> [@Colin](#):
>
> So that suggests that maybe something has changed and that flag is now set to true where it was set to false (or maybe defaulting to false) previously.

good catch & very likely.

Maybe a feature request for adding that option to the mysql connection config node is in order.

Another option would be the ability to add any additional flags you desire (that the mysql lib supports) via a JSON only typedInput that are then passed to the connection?

---

<div class="post-metadata">

### Author: ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)
#### Post date: [21 March 2021 15:09 UTC](https://discourse.nodered.org/t/function-node-getfullyear-error/42926/20 "2021-03-21T15:09:50Z")

</div>

the connection we use is [here](https://github.com/node-red/node-red-nodes/blob/master/storage/mysql/68-mysql.js#L35)

so no dateStrings setting there...

[Next page](https://discourse.nodered.org/t/function-node-getfullyear-error/42926.md?page=2)