Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
One load statement can't refer to field names in completely different table. So after loading the dates, I believe you need to do this:
LET vStartDate = peek('startdate');
LET vEndDate = peek('enddate');
...
FROM SolitonServer.dbo.STUDY
WHERE STUDYDATE > '$(vStartDate)'
AND STUDYDATE < '$(vEndDate)';
It is possible you will need to fiddle with the variables to get the correct format to match the studydate, but I'm thinking you won't have to as long as your XML source has them looking correct.
Hi Dan,
Can you check data type for the field STUDYDATE. Does it is DateTime or Char? What is the formate for this field?
or you can try where STUDYDATE > '12-10-2010' and STUDYDATE < '21-01-2011'
Regards,
Sokkorn Cheav
Hi Sokkorn Cheav,
In my SQL table the Data Type of the field STUDYDATE is 'datetime'. So the format is DD/MM/YYYY HH:MM:SS.
Regards,
Dan
Hi All,
I've had some success now. Not sure what I was doing wrong but the following line seems to work fine now:
FROM SolitonServer.dbo.STUDY where STUDYDATE > '12/11/2003 00:00:00' and STUDYDATE < '01/01/2011 00:00:00';
Now I want to change these date values to be read from an xml file. I have the following script for a 'dates' table:
// Start of [daterange.xml] LOAD statements
dates:
LOAD startdate
enddate
FROM [..\..\daterange.xml] (XmlSimple, Table is [dates]);
// End of [daterange.xml] LOAD statements
So I thought I would be able to change my statement in the STUDY table to read:
FROM SolitonServer.dbo.STUDY where STUDYDATE > startdate and STUDY.STUDYDATE < enddate;
But I get the following error:
Invalid column name 'enddate'.
Any Ideas?
Thanks,
Dan
Hi Dan,
I think you forgot comma sign (,)
Look like this:
// Start of [daterange.xml] LOAD statements
dates:
LOAD
startdate, //Maybe this point (,)
enddate
FROM [..\..\daterange.xml] (XmlSimple, Table is [dates]);
// End of [daterange.xml] LOAD statements
Regards,
Sokkorn Cheav
One load statement can't refer to field names in completely different table. So after loading the dates, I believe you need to do this:
LET vStartDate = peek('startdate');
LET vEndDate = peek('enddate');
...
FROM SolitonServer.dbo.STUDY
WHERE STUDYDATE > '$(vStartDate)'
AND STUDYDATE < '$(vEndDate)';
It is possible you will need to fiddle with the variables to get the correct format to match the studydate, but I'm thinking you won't have to as long as your XML source has them looking correct.
Going back to the out of range datetime value, are you sure the format is DD/MM/YYYY HH:MM:SS and not MM/DD/YYYY HH:MM:SS since you got the error when you used '21/01/2011 00:00:00' and not when you used '01/01/2011 00:00:00'
Thanks John and Karl,
John, your solution with setting the dates as variables worked great.
Karl, you were right about the date format, I think I was getting confused as the date was being displayed in DD/MM/YYYY in Qlikview but I assume that Qlikview was changing the format for me.
Problem Solved, thanks again!
Dan