[Logo] SpagoWorld Forums
  [Search] Search   [Recent Topics] Recent Topics   [Hottest Topics] Hottest Topics   [Members]  Member Listing  
[Groups] Back to home page  [Moderation Log] Moderation Log   [Register] Register /  [Login] Login 
QBE date/timestamp format fix for en_US  XML
Forum Index » Technical Help - SpagoBI Server - FREE INQUIRY
Author Message
sgjava.spw

Newby

Joined: 15/07/2010 20:06:54
Messages: 10
Offline

I have changed all the date formats to MM/dd/yyyy in spagobi.xml and various javascript files (a centralized date mask would be better). In the log it shows TO_TIMESTAMP('12/31/2009 00:00:00.00','DD/MM/YYYY HH24:MI:SS.FF') and I cannot find 'DD/MM/YYYY HH24:MI:SS.FF' with a text search of the SpagoBI root.

This message was edited 2 times. Last update was at 12/07/2011 17:41:28

angioia

SpagoBI hero
[Avatar]

Joined: 12/04/2010 15:41:11
Messages: 562
Location: Milan, Italy
Offline

Sorry but i have not understand what you want to do exactley. Do you want to change the format used for dates in the result's grid?

Let me know

Andrea Gioia
-------------------------
@andrea_gioia
@geobinitiative
-------------------------
[WWW]
sgjava.spw

Newby

Joined: 15/07/2010 20:06:54
Messages: 10
Offline

I'm talking about the Right operand of the Where clause I have to use '10/06/2010 00:00:00.00' (which is 'DD/MM/YYYY HH24:MI:SS.FF' format) instead of en_US style of 'MM/DD/YYYY HH24:MI:SS.FF'. If you look in the log you see:

audit.query.service:187 - [biuser]:: max result limit [10000] exceeded with SQL: select viwauditcu0_.datecompleted as col_0_0_ from public.viwauditcube_withlpmanager viwauditcu0_ where viwauditcu0_.datecompleted>to_timestamp('10/06/2010 00:00:00.00', 'DD/MM/YYYY HH24:MI:SS.FF').

So, SpagoBI is not picking up my date formats from spagobi.xml:


I cannot find 'DD/MM/YYYY HH24:MI:SS.FF' in any of the config files.
sgjava.spw

Newby

Joined: 15/07/2010 20:06:54
Messages: 10
Offline

Any ideas, this is a major problem if QBE cannot handle en_US dates for operands. I will try setting all the date formats to en_US format just to see which QBE is picking up.

This message was edited 1 time. Last update was at 12/07/2011 17:20:06

sgjava.spw

Newby

Joined: 15/07/2010 20:06:54
Messages: 10
Offline

I finally had to revisit this issue in SpagoBI and came up with a solution this time. It involves modifications to both client and server code (I really didn't want to go there) and configurations. This is the build process for SpagoBI 2.8 and fixes for the Demo release (you could also just fix the source version and build a deployable from scratch). Other versions may require different steps to apply fixes and configuration. This should work on all database dialects, but it's only tested under Hypersonic and Postgres.

Assumptions:

  • Ubuntu 10.04
  • Sun JDK
  • Postgres 9.0
  • SpagoBI configured for Postgres JNDI datasource
  • Understanding of SpagoBI configuration and build process
  • Ant installed and in the execution path
  • SRC_HOME is the SpagoBI source root
  • DEST_HOME is the SpagoBI demo root

  • Download SpagoBI source and uncompress http://forge.ow2.org/project/showfiles.php?group_id=204

    Download Tomcat 6 and umcompress to SRC_HOME/dist http://tomcat.apache.org/download-60.cgi

    Change client and server based dates/timestamps formats to be en_US format

  • DEST_HOME/webapps/SpagoBIQbeEngine/js/spagobi/locale/commons/en.js
  • DEST_HOME/webapps/SpagoBIQbeEngine/js/spagobi/qbe-all-2.8.0.js
  • DEST_HOME/webapps/SpagoBIQbeEngine/js/spagobi/widgets/FilterLookupField.js
  • DEST_HOME/webapps/SpagoBI/js/src/ext/sbi-all-ext311-2.8.0.js
  • DEST_HOME/webapps/SpagoBI/WEB-INF/conf/config/spagobi.xml
  • DEST_HOME/webapps/SpagoBI/WEB-INF/conf/config/mail.xml
  • DEST_HOME/webapps/SpagoBI/WEB-INF/conf/config/data_access.xml

  • Find timestamp formatting literals in source to that need to be changed


  • SRC_HOME/QbeCore/src/it/eng/qbe/statment/hibernate/HQLStatement.java
  • SRC_HOME/QbeCore/src/it/eng/qbe/statment/jpa/JPQLStatement.java
  • SRC_HOME/SpagoBIProject/src/it/eng/spagobi/behaviouralmodel/lov/bo/QueryDetail.java

  • Change timestamp formatting literals in entire source tree


    Build source

    Edit SRC_HOME/ant-files/SpagoBI-2.x-source/SpagoBIProject/ant/build.xml and comment out the following (fixes build failure http://www.spagoworld.org/jforum/posts/list/487.page):

    Edit SRC_HOME/ant-files/SpagoBI-2.x-source/ant/ and change tomcat.home= SRC_HOME/dist/apache-tomcat-6.0.xx

    From shell execute

    This message was edited 10 times. Last update was at 12/07/2011 20:42:06

    sgjava.spw

    Newby

    Joined: 15/07/2010 20:06:54
    Messages: 10
    Offline

    Here's some more date formatting fixes that live outside QBE. You have to use your best judgment by inspecting each file to see if changes are really needed otherwise a global serach and replace would have worked.

    Change client and server based dates/timestamps formats to be en_US format. Use the find command to locate the various patterns.


  • DEST_HOME/webapps/SpagoBIQbeEngine/js/spagobi/locale/commons/en.js
  • DEST_HOME/webapps/SpagoBIGeoReportEngine/js/src/ext/sbi/locale/en.js
  • DEST_HOME/webapps/SpagoBIConsoleEngine/js/spagobi/locale/en.js


  • DEST_HOME/webapps/SpagoBI/WEB-INF/conf/config/spagobi.xml
  • DEST_HOME/webapps/SpagoBI/WEB-INF/conf/tools/scheduler/formulas.xml
  • DEST_HOME/webapps/SpagoBI/WEB-INF/jsp/tools/scheduler/TriggerDetail.jsp


  • DEST_HOME/webapps/SpagoBIQbeEngine/js/spagobi/qbe-all-2.8.0.js
  • DEST_HOME/webapps/SpagoBIQbeEngine/js/spagobi/widgets/FilterLookupField.js
  • DEST_HOME/webapps/SpagoBIQbeEngine/js/spagobi/commons/JSON.js
  • DEST_HOME/webapps/SpagoBI/js/src/ext/sbi-all-ext311-2.8.0.js
  • DEST_HOME/webapps/SpagoBI/js/src/ext/sbi/kpi/ManageGoalsDetailsPanel.js
  • DEST_HOME/webapps/SpagoBI/js/src/ext/sbi/kpi/ManageOUGrants.js
  • DEST_HOME/webapps/SpagoBIConsoleEngine/js/spagobi/console/PromptablesWindow.js
  • DEST_HOME/webapps/SpagoBIConsoleEngine/js/spagobi/commons/JSON.js


  • DEST_HOME/webapps/SpagoBI/WEB-INF/conf/config/data_access.xml
  • test.spw



    Joined: 06/07/2010 17:53:44
    Messages: 1
    Offline

    Hello everybody !!

    I've tried all your solutions but it doesn't work. I've setup Spagobi 3.0 with tomcat 6.0.18.

    I've created a datamart, insert it in Spagobi (with "DM" like folder name). In the platform, I've created a datamart model document, with a template.
    The datamart is working, everything is working. The only thing which isn't working is the date field when I want to create a filter on it, in a QBE.
    I always have this error : An error occurred in EXEC_QUERY_ACTION service while executing query: [SELECT t_0.datecal FROM Calendrier t_0 WHERE t_0.datecal<]

    Can you please help I'm stuck.....
    sgjava.spw

    Newby

    Joined: 15/07/2010 20:06:54
    Messages: 10
    Offline

    You didn't show your date format from the log which is hard coded to 'DD/MM/YYYY HH24:MI:SS.FF'. If you make the changes above and build from source it will fix the date/timestamp issue (in 2.8 at least).

    One other thing you need to do is clear your browser cache to clear up any cached javascript with the old date/timestamp masks.

    This message was edited 1 time. Last update was at 19/07/2011 18:13:25

    johnnnnn.spw



    Joined: 24/10/2013 00:23:00
    Messages: 4
    Offline

    Hello

    For anyone trying to make a LOV and Analytical Driver with date format like this '2014-02-10'
    you can do the following:

    SELECT distinct
    (
    CONVERT(varchar, DATEPART(yyyy, MY_TABLE.DATE_FIELD))
    + '-'
    + RIGHT('00' + CONVERT(varchar, DATEPART(mm, MY_TABLE.DATE_FIELD)), 2)
    + '-'
    + RIGHT('00' + CONVERT(varchar, DATEPART(dd, MY_TABLE.DATE_FIELD)), 2)
    )
    AS
    DATE_FIELD
    FROM
    MY_TABLE.

    Go ahead and test it first on your SQL Manager

     
    Forum Index » Technical Help - SpagoBI Server - FREE INQUIRY
    Go to:   
    Powered by JForum 2.1.9 © JForum Team