Community Tip - Visit the PTCooler (the community lounge) to get to know your fellow community members and check out some of Dale's Friday Humor posts! X
Dear everyone,
I stucked with the problem that input variable could not put into sql statement,Thingworx give error "
"
my sql statement like:
select EXTRACT(MONTH from TIMESTAMP '[[ddd]]'); //ddd is the input variable ,Datetime type
if I put the ddd with '2016-12-01 08:40:00', thingworx will give error.
thank you in advance.
Solved! Go to Solution.
Thank you Mahesh ,I have solved the problem,just change the query into "select EXTRACT(MONTH from [[ddd]]::TIMESTAMP);" 。
I don't know why , just keep trying.
Hi Xiang Jing Guo,
I hope ddd is the input variable.
Try following query:
select EXTRACT(MONTH from TIMESTAMP [[ddd]]);
input variable should not be in quotes.
It works fine for me.
I hope it helps.
Thanks,
Ankit Gupta
Dear Ankit,
I tryed the query you mentioned above,but get the following error:
ERROR: syntax error at or near "$1" .postion 37.
I use the postgresql as the database .
Thanks anyway.
Hi Xiang Jing Guo,
Have you tried with adding "Date/Time stamp" pattern in your query?
Like 'dd-mm-yyyy, yyyy-mm-dd' etc.
In my scenario, I used as suggested by Ankit and added specific pattern and it works,
select * from "FOO"
where
to_date(START_DATE)
BETWEEN
to_date([[StartDate]], 'dd-mm-yyyy')
AND
to_date([[EndDate]], 'dd-mm-yyyy')
I will suggest, first try to run the query which you are looking for on your database and then replicate it on ThingWorx.
Hope it helps.
Regards,
Mahesh Lohokare
Thank you Mahesh ,I have solved the problem,just change the query into "select EXTRACT(MONTH from [[ddd]]::TIMESTAMP);" 。
I don't know why , just keep trying.
Hey, if one of these answers helped you, do you think you could mark it as correct so other community users can more easily resolve similar issues? Thanks!
yes Tori,but I didn't find where to mark .