# NodeRED and SQL data fetching problem

**URL:** <https://discourse.nodered.org/t/nodered-and-sql-data-fetching-problem/10119>\
**Category:** General\
**Created:** [14 April 2019 09:51 UTC](https://discourse.nodered.org/t/nodered-and-sql-data-fetching-problem/10119 "2019-04-14T09:51:13Z")\
**Posts on this page:** 15\
**Page:** 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:** [14 April 2019 11:05 UTC](https://discourse.nodered.org/t/nodered-and-sql-data-fetching-problem/10119/21 "2019-04-14T11:05:02Z")

</div>

I know it isn't what you want, but we are trying to get a clue as to what is going on. Is it an issue with the WHERE or can't you retrieve anything at all.  
Also it might be worth adding the payload in to the failing one, with an empty string payload, and removing the fromDate and toDate fields, just in case they are confusing the node.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [14 April 2019 11:08 UTC](https://discourse.nodered.org/t/nodered-and-sql-data-fetching-problem/10119/22 "2019-04-14T11:08:40Z")

</div>

Not sure if ths is part of the issue but if you notice in yout debug topic one where clause is:  
`WHERE Time BETWEEN '2019-4-12'AND'2019-4-12';`  
and the other is  
`WHERE Time BETWEEN '2019-04-12' AND'2019-04-12';`  
notice the lack of spaces befora and after the `AND`?

Just be sure, please fix that and try again.

(@colin I don't have better eyes, I just copied the picture to my desktop and enalarged it 😊)

---

<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:** [14 April 2019 11:10 UTC](https://discourse.nodered.org/t/nodered-and-sql-data-fetching-problem/10119/23 "2019-04-14T11:10:46Z")

</div>

Also one says -04- for April and the other says -4-  
I suspect this maybe more likely cause if it can't parse the date correctly it won't return any data

---

<div class="post-metadata">

**Author:** ![ALOKA1432](https://avatars.discourse-cdn.com/v4/letter/a/3ab097/32.png) [@ALOKA1432](https://discourse.nodered.org/u/ALOKA1432)\
**Post date:** [14 April 2019 11:10 UTC](https://discourse.nodered.org/t/nodered-and-sql-data-fetching-problem/10119/24 "2019-04-14T11:10:56Z")

</div>

Same result after fixing the space.

---

<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:** [14 April 2019 11:11 UTC](https://discourse.nodered.org/t/nodered-and-sql-data-fetching-problem/10119/25 "2019-04-14T11:11:15Z")

</div>

> [@ALOKA1432](#):
>
> node-red-contrib-mysql

The info for that node is pathetic. It doesn't even tell you that you can put the query in the topic. Are you sure that is what you are supposed to do?  
Can you show us the setup you have entered for the mssql node?

---

<div class="post-metadata">

**Author:** ![ALOKA1432](https://avatars.discourse-cdn.com/v4/letter/a/3ab097/32.png) [@ALOKA1432](https://discourse.nodered.org/u/ALOKA1432)\
**Post date:** [14 April 2019 11:12 UTC](https://discourse.nodered.org/t/nodered-and-sql-data-fetching-problem/10119/26 "2019-04-14T11:12:44Z")

</div>

![Screenshot%20(17)](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/e/e54b5431671cd722d739751ceef866ac4f531a4d.png)

---

<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:** [14 April 2019 11:17 UTC](https://discourse.nodered.org/t/nodered-and-sql-data-fetching-problem/10119/27 "2019-04-14T11:17:15Z")

</div>

Are you sure that is node-red-contrib-mssql, mine doesn't look like that.

---

<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:** [14 April 2019 11:19 UTC](https://discourse.nodered.org/t/nodered-and-sql-data-fetching-problem/10119/28 "2019-04-14T11:19:57Z")

</div>

Also why are the zeros now showing with cross lines on them when they weren't previously?

---

<div class="post-metadata">

**Author:** ![ALOKA1432](https://avatars.discourse-cdn.com/v4/letter/a/3ab097/32.png) [@ALOKA1432](https://discourse.nodered.org/u/ALOKA1432)\
**Post date:** [14 April 2019 11:20 UTC](https://discourse.nodered.org/t/nodered-and-sql-data-fetching-problem/10119/29 "2019-04-14T11:20:38Z")

</div>

Hey dcejay, You saved me man. It worked. What i was doing is that i was getting the date using var date = new date();  
var monthUpdated = date.getMonth()+1;  
So it was missing the 0 before the month (4 instead of 04).  
So i added one 0 before the monthUpdated and it worked.😃  
The datepicker default generated anextra zero before the month.

 ![Screenshot%20(18)](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/4/4acac78f34ce3a5b21f9339f60557042d9f8a239.png) ![Screenshot%20(19)](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/c/cc2adc561a320d6aede112e9e401e46defed67e4.png)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [14 April 2019 11:23 UTC](https://discourse.nodered.org/t/nodered-and-sql-data-fetching-problem/10119/30 "2019-04-14T11:23:49Z")

</div>

@Colin it is MySQL not msSQL

---

<div class="post-metadata">

**Author:** ![ALOKA1432](https://avatars.discourse-cdn.com/v4/letter/a/3ab097/32.png) [@ALOKA1432](https://discourse.nodered.org/u/ALOKA1432)\
**Post date:** [14 April 2019 11:25 UTC](https://discourse.nodered.org/t/nodered-and-sql-data-fetching-problem/10119/31 "2019-04-14T11:25:46Z")

</div>

@zenofmud @Colin @dceejay, Thanks all of you for storming your brains.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [14 April 2019 11:34 UTC](https://discourse.nodered.org/t/nodered-and-sql-data-fetching-problem/10119/32 "2019-04-14T11:34:02Z")

</div>

You might want to think about storing date/times in datetime formats instead of as (I assume) text. It makes life easier in the long-term

---

<div class="post-metadata">

**Author:** ![ALOKA1432](https://avatars.discourse-cdn.com/v4/letter/a/3ab097/32.png) [@ALOKA1432](https://discourse.nodered.org/u/ALOKA1432)\
**Post date:** [14 April 2019 11:43 UTC](https://discourse.nodered.org/t/nodered-and-sql-data-fetching-problem/10119/33 "2019-04-14T11:43:21Z")

</div>

sure sir. you guys have been truly helpful in finding the bug.

---

<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:** [14 April 2019 12:42 UTC](https://discourse.nodered.org/t/nodered-and-sql-data-fetching-problem/10119/34 "2019-04-14T12:42:19Z")

</div>

Ah, yes node-red-contrib-mysql. I said my eyes were going.  
Brain too probably ☹

---

<div class="post-metadata">

**Author:** ![Brian5600](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/brian5600/32/94271_2.png) [@Brian5600](https://discourse.nodered.org/u/Brian5600)\
**Post date:** [14 April 2019 18:14 UTC](https://discourse.nodered.org/t/nodered-and-sql-data-fetching-problem/10119/35 "2019-04-14T18:14:07Z")

</div>

Hello Aloka1432  
Be aware, that Your code always put an extra zero in front of the month, also when 10, 11, 12. So when you hit october, Your code will break again.  
Best regards  
Brian

[Previous page](https://discourse.nodered.org/t/nodered-and-sql-data-fetching-problem/10119.md?page=1)