I have seen threads on how to set up the current date in DB2 and AS400, but every time I go into the universe to set up an object for either, I consistently get error messages.
I would think all I need to do is type Current Date for DB2 or CURDATE for AS400
Here is the message I get for both:
Parse failed: [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL104 - Token was not valid. Valid token: ( <TABLE_IDENTIFIER>.
I also worked with a DBA to try different date combinations to no avail. Things like sysdate, current_date, cur_date, currentdate(),
And on top of that, I need to get the current date into a yyyymmdd format, or I need to find a way to convert the data in the report. Just switching the number in the data to a date switches the data from 20050401 to 03/15/84 because BO treats the number like it is in seconds.
Getting a bit frustrated as to why this isn’t working?
Any help would be greatly appreciated. Thank you!!
Neal
I guess the question I have now is trying to get the dates in the report to compare.
The report is connected to DB2 with the dates as numeric (format yyyymmdd) and I did a date(numeric object) object to convert from numeric to date. Ran the report I got a SQL Connection error stating a data conversion or mapping error.
Am I able to do a conversion from the numeric format, or will I need to take the current date and convert it to the DB2-style format?
In Oracle, you have to convert from the date format that the date is stored in, to the date format that you want to display. So, maybe you’re not correctly converting from the existing format?
I don’t understand why Current Date isn’t in date format by default. I’ve never had that trouble. I open object properties, put in Current Date for the select, change type to Date and it works.
this is DB2 UDB and Z series - can’t answer to AS400 tho.
In DB2 since there’s only character and numeric formats, that lies the problem. I can do a Current Date function and I’ll get 5/11/05.
If I do a date conversion of a numeric data type and I get the forementioned SQL connection error. I was hoping to either get the current date to a numeric format, or get the dates in the report from numeric to a mm/dd/yy format so I can do some of the functions such as DaysBetween in the report.
What we’re trying to do right now is convert the date to character, which we were able to do in the universe by a char() function - but then trying to do a ToDate(,“mm/dd/yy”) - I get #ERROR
Maybe there is a time portion of the field that needs to be addressed. So when you do ToDate(,“mm/dd/yy”), it runs out of format specifier (“mm/dd/yyyy”) before it has converted the entire string.
What happens if you do ToDate(,“mm/dd/yy hh:ss”) or something similar?
I did do a search on DB2 dates, just didn’t see what I was looking for. I’ll try what you posted, Eileen and let the forum know any solution I come up with.
I seemed to have found a way to get Current Date in DB2 to convert from Date to Characters as an object in the universe. Then I was able to use this Current Date object as a constraint in my reports, and it worked great. The DATE format from our database is yyyy-mm-dd.
In the universe I created an object, called it Current Date, then used the following syntax for the select:
REPLACE(CHAR(CURRENT DATE),’-’,’’)
Make sure you have Character in the TYPE drop down. Under Object Format I have it set to yyyymmdd. I saved this and went to my report where I used a constraint of Begin_DT < Current Date, where Begin_DT is a universe object, and current date is a universe object.
Hope this helps anyone with a similiar problem.
(BOB member since 2003-09-25)
(BOB member since 2003-09-25)
I seemed to have found a way to get Current Date in DB2 to convert from Date to Characters as an object in the universe. Then I was able to use this Current Date object as a constraint in my reports, and it worked great. The DATE format from our database is yyyy-mm-dd.