in cell C3 I have this=NL("rows","Quotes - Original",{"Trip Departure Date"},"Trip Departure Date", "2010-01-01..")
In cell D3 I have this:=NF(C3,"Trip Departure Date")
It is returning Trip Departure Dates from 12/31/2009 and above. I am not sure why this is happening. Also can anyone explain how the date/number works in Jet Reports? I may be getting confused by this if I am understanding this wrong. To me, it is converting the date, 2012-01-01 to the number of days past january 1 1900, and then running the code on that number but I am not sure why it is comparing as a day before the entered date.
Date
Votes
11 comments
-
Jet Reports Historic Posts I think I solved this conundrum. Jet Reports is returning an integer, To convert to a short date Excel would be adding that number of days to January 1st 1900, Excel seems to believe that February 29th was a leap year in 1900 when it was not which is changing the integer to date conversion off by 1.
Does this sound right to anyone else? -
Jet Reports Historic Posts Hmmm, no this doesn't seem quite right to me, but it may depend on what is actually in your database. Jet does not return dates to Excel as integers. It returns them as DateTime objects which Excel itself turns into integers (assuming your field is actually a date type field in the database and not an integer field). You could be experiencing some sort of time zone conversion problem. Your database could be storing things in UTC or in local time but your own time zone is different so it appears that the dates are a day off.
Let's try a little experiment and try writing a SQL query to get us the dates in the database formatted as text and see what happens. I'm assuming you're using a SQL Server database, so the SQL may have to be modified if it's something else. The function might look like this:=NL("Rows","SQL=SELECT DISTINCT convert(varchar,"Trip Departure Date") AS Date FROM "Quotes - Original" WHERE ("Trip Departure Date" >= {ts '2010-01-01 00:00:00'}","Date")
What does this function return to you?
Regards,
Hughes -
Jet Reports Historic Posts First off, the database is a pervasive sql database. I do not know what format the dates are stored in.
I tried to run the sql query but it is failing at the quotations in 'Trip Departure Date"
=NL("Rows","SQL=SELECT DISTINCT convert(varchar,"Trip Departure Date") AS Date FROM "Quotes - Original" WHERE ("Trip Departure Date" >= {ts '2010-01-01 00:00:00'}","Date")
I had tried to use the sql command previously but was stuck at not being able to access any tables/names with spaces due to this issue. I tried various escapes but could not find one that works. -
Jet Reports Historic Posts You should be able to tell what sort of quoting Pervasive uses by taking your previous NL(Rows) formula (not the one I made with SQL=) and add the filter "ShowQuery=",true like this:
=NL("rows","Quotes - Original",{"Trip Departure Date"},"Trip Departure Date", "2010-01-01..","ShowQuery=",true)
Then you can see the SQL query Jet is sending in and then it should become obvious what sort of quotes are necessary. Does that help?
Regards,
Hughes -
Jet Reports Historic Posts SELECT DISTINCT "Trip Departure Date" FROM "QUOTES - original" WHERE ("Trip Departure Date" >= 2010-01-01) ORDER BY "Trip Departure Date"
The issue though, is that when I embed that sql query into the excel function box it won't accept it.
When I do:
=NL("Rows","SQL=SELECT DISTINCT convert(varchar,"Trip Departure Date") AS Date FROM "Quotes - Original" WHERE ("Trip Departure Date" >= {ts '2010-01-01 00:00:00'}","Date")
The first set of quotations before 'Trip Departure Date' closes the opening quotations that outline the SQL query. Then it views Trip Departure Date as an excel function that doesn't exist, and won't parse the rest of the function. -
Jet Reports Historic Posts Oh right, sorry, you just need to do double quotes for that. Looks like the format for the date literal is slightly different as well. Try this:
=NL("Rows","SQL=SELECT DISTINCT convert(varchar,""Trip Departure Date"") AS Date FROM ""Quotes - Original"" WHERE (""Trip Departure Date"" >= 2010-01-01) ORDER BY ""Trip Departure Date""","Date")
I'm still not sure it will work because I don't know if the convert function exists in Pervasive SQL or if varchar is a valid data type or if aliasing with the AS keyword works. Anyway, it's still worth a try.
Regards,
Hughes -
Jet Reports Historic Posts I ran it as that, and it threw this error:
The second parameter Trip Departure Date for CONVERT is invalid.
When I removed the convert, it returned an integer… the number 40178. -
Jet Reports Historic Posts Hmmm, well that sort of spoils the point of this test. The convert function in the SQL query was converting the date field to a string so that it would get returned to Excel as a string. Without the convert function, we can't tell if the field is actually getting returned from the database as a date or as an integer. As I said, when we return dates to Excel, they will often appear as numbers unless you format the cell as a date. I'm looking around online and it looks like you might be able to replace the convert with a cast function in Pervasive SQL like this:
CAST("Trip Departure Date" as VARCHAR(25))
What happens when you try this in your formula?
Regards,
Hughes -
Jet Reports Historic Posts First off thanks for taking the time to help me with this.
After changing it from convert to cast, it is returning a 5 digit number such as 40178, so I believe the query is actually returning a number and pervasive doesn't treat the year 1900 as a leap year which gives us the one day difference as excel does according to this: http://support.microsoft.com/kb/214326/en-us -
Jet Reports Historic Posts Okay so what that says to me is that inside the Pervasive SQL database, the date is being stored as a number, which is interesting. So Jet is getting the data from Pervasive SQL as a number, not as a DateTime object, which is what we get with most other databases. Then Jet is returning that number to Excel and Excel's date number system and Pervasive's date number system do not match up. Very interesting. It could be something to do with the year 1900 like you say; I've read somewhere before that Excel's date number system has a few anomilies so it may be you've found one. It's interesting that the 2 numbering systems are even as close as they are. Sounds like your solution to just add or subtract a day from the result may be the best way to go.
Regards,
Hughes -
Jet Reports Historic Posts Thanks for working with me through this, I will see if I can get some data from the database administrators regarding how it handles the dates to confirm this.
Please sign in to leave a comment.