NodeRED and SQL data fetching problem

General
21

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.

22

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 :blush:)

23

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

24

Same result after fixing the space.

25

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?

26

Screenshot%20(17)
Screenshot (17).png1366×768 71.4 KB

27

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

28

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

29

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.:smiley:
The datepicker default generated anextra zero before the month.

Screenshot%20(18)
Screenshot (18).png1366×768 44.5 KB
Screenshot%20(19)
Screenshot (19).png1366×768 67.4 KB

30

@Colin it is MySQL not msSQL

31

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

32

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

33

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

1 Like
34

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

1 Like
35

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

1 Like