TimeStamp(Oracle)/DateTime(MS SQL) issue. Not able to get report

Hi All, 
 
I have a table in MS SQL say "sriraman_test". There are two columns one with "Name" varchar and other with the "DATETIME" as datetime (8/26/2010 1:03:34 PM format). A similar table also exists in oracle with the timestamp (01-Sep-10 01:00:00.000000 PM format)instead of datetime. 
 
1. With birt version 2.2.1 I am not able to create a report for the below select query 
 
"select * from DASH1.SRIRAMAN_TEST where DASH1.SRIRAMAN_TEST.DATETIME = ?". I get an error message as below 
 
The following items have errors: 
 
 
ReportDesign (id = 1): 
+ Cannot get the result set metadata. 
SQL statement does not return a ResultSet object. 
SQL error #1: ORA-01843: not a valid month 
 
However with birt version 2.6, I am able to create a proper report for the query above. 
 
2. However for similar query on MS SQL I am able to create a report in version 2.2.1 but in version 2.6 same query fails i.e. I dont get any error messages, but the report is blank though I have proper values which should come up on report. The query for MS SQL is as below 
 
"select * from dbo.sriraman_test where dbo.sriraman_test.DATETIME = ?" 
 
Below are the design files. I have to paste here since I am unable to attach them. 
 
 
=========================================================================================== 
mssql_datetime_2_2_1 
=========================================================================================== 
[xml] 
<?xml version="1.0" encoding="UTF-8"?> 
<report xmlns="http-~~-//www.eclipse.org/birt/2005/design" version="3.2.15" id="1"> 
<property name="createdBy">Eclipse BIRT Designer Version 2.2.1.r221_v20070924 Build <2.2.0.v20070924-1550></property> 
<property name="units">in</property> 
<property name="comments">Copyright (c) 2007 <<Your Company Name here>></property> 
<html-property name="description">Creates a blank report with no predefined content.</html-property> 
<text-property name="displayName">Blank Report</text-property> 
<property name="iconFile">/templates/blank_report.gif</property> 
<parameters> 
<scalar-parameter name="param_1" id="24"> 
<property name="valueType">static</property> 
<property name="dataType">dateTime</property> 
<property name="paramType">simple</property> 
<property name="isRequired">true</property> 
<property name="controlType">text-box</property> 
<property name="defaultValue">08/26/2010 01:03:34 PM</property> 
<property name="distinct">true</property> 
<structure name="format"> 
<property name="category">Unformatted</property> 
</structure> 
</scalar-parameter> 
</parameters> 
<data-sources> 
<oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="Data Source" id="6"> 
<text-property name="displayName"></text-property> 
<list-property name="privateDriverProperties"> 
<ex-property> 
<name>ODAPerspective</name> 
<value>Birt</value> 
</ex-property> 
<ex-property> 
<name>ODAUsageHelperConnection</name> 
<value>Native</value> 
</ex-property> 
</list-property> 
<property name="odaDriverClass">net.sourceforge.jtds.jdbc.Driver</property> 
<property name="odaURL">jdbc:jtds:sqlserver://10.113.58.41/dash1;Instance=;PrepareSQL=0</property> 
<property name="odaUser">sa</property> 
<encrypted-property name="odaPassword" encryptionID="base64">d2VsY29tZQ==</encrypted-property> 
</oda-data-source> 
</data-sources> 
<data-sets> 
<oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" name="Data Set" id="7"> 
<list-property name="columnHints"> 
<structure> 
<property name="columnName">name</property> 
<property name="displayName">name</property> 
</structure> 
<structure> 
<property name="columnName">datetime</property> 
<property name="displayName">datetime</property> 
</structure> 
</list-property> 
<structure name="cachedMetaData"> 
<list-property name="resultSet"> 
<structure> 
<property name="position">1</property> 
<property name="name">name</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">2</property> 
<property name="name">datetime</property> 
<property name="dataType">date-time</property> 
</structure> 
</list-property> 
</structure> 
<property name="dataSource">Data Source</property> 
<list-property name="parameters"> 
<structure> 
<property name="name">param_1</property> 
<property name="paramName">param_1</property> 
<property name="nativeName"></property> 
<property name="dataType">dateTime</property> 
<property name="nativeDataType">1</property> 
<property name="position">1</property> 
<property name="isOptional">false</property> 
<property name="allowNull">false</property> 
<property name="isInput">true</property> 
<property name="isOutput">false</property> 
</structure> 
</list-property> 
<list-property name="resultSet"> 
<structure> 
<property name="position">1</property> 
<property name="name">name</property> 
<property name="nativeName">name</property> 
<property name="dataType">string</property> 
<property name="nativeDataType">12</property> 
</structure> 
<structure> 
<property name="position">2</property> 
<property name="name">datetime</property> 
<property name="nativeName">datetime</property> 
<property name="dataType">date-time</property> 
<property name="nativeDataType">93</property> 
</structure> 
</list-property> 
<property name="queryText">select * 
from dbo.sriraman_test 
where dbo.sriraman_test.DATETIME = ?</property> 
<xml-property name="designerValues"><![CDATA[<?xml version="1.0" encoding="UTF-8"?> 
 
<model:DesignValues xmlns:design="http-~~-//www.eclipse.org/datatools/connectivity/oda/design" xmlns:model="http-~~-//www.eclipse.org/birt/report/model/adapter/odaModel"> 
 
<Version>1.0</Version> 
 
<design:DataSetParameters> 
 
<design:parameterDefinitions> 
 
<design:inOutMode>In</design:inOutMode> 
 
<design:attributes> 
 
<design:name></design:name> 
 
<design:position>1</design:position> 
 
<design:nativeDataTypeCode>1</design:nativeDataTypeCode> 
 
<design:nullability>Unknown</design:nullability> 
 
<design:uiHints/> 
 
</design:attributes> 
 
<design:inputAttributes> 
 
<design:elementAttributes> 
 
<design:defaultScalarValue>08/26/2010 01:03:34 PM</design:defaultScalarValue> 
 
<design:optional>false</design:optional> 
 
<design:masksValue>false</design:masksValue> 
 
<design:uiHints> 
 
<design:promptStyle>TextField</design:promptStyle> 
 
</design:uiHints> 
 
</design:elementAttributes> 
 
</design:inputAttributes> 
 
</design:parameterDefinitions> 
 
</design:DataSetParameters> 
 
<design:ResultSets derivedMetaData="true"> 
 
<design:resultSetDefinitions> 
 
<design:resultSetColumns> 
 
<design:resultColumnDefinitions> 
 
<design:attributes> 
 
<design:name>name</design:name> 
 
<design:position>1</design:position> 
 
<design:nativeDataTypeCode>12</design:nativeDataTypeCode> 
 
<design:precision>50</design:precision> 
 
<design:scale>0</design:scale> 
 
<design:nullability>Nullable</design:nullability> 
 
<design:uiHints> 
 
<design:displayName>name</design:displayName> 
 
</design:uiHints> 
 
</design:attributes> 
 
<design:usageHints> 
 
<design:label>name</design:label> 
 
<design:formattingHints> 
 
<design:displaySize>50</design:displaySize> 
 
</design:formattingHints> 
 
</design:usageHints> 
 
</design:resultColumnDefinitions> 
 
<design:resultColumnDefinitions> 
 
<design:attributes> 
 
<design:name>datetime</design:name> 
 
<design:position>2</design:position> 
 
<design:nativeDataTypeCode>93</design:nativeDataTypeCode> 
 
<design:precision>23</design:precision> 
 
<design:scale>3</design:scale> 
 
<design:nullability>Nullable</design:nullability> 
 
<design:uiHints> 
 
<design:displayName>datetime</design:displayName> 
 
</design:uiHints> 
 
</design:attributes> 
 
<design:usageHints> 
 
<design:label>datetime</design:label> 
 
<design:formattingHints> 
 
<design:displaySize>23</design:displaySize> 
 
</design:formattingHints> 
 
</design:usageHints> 
 
</design:resultColumnDefinitions> 
 
</design:resultSetColumns> 
 
</design:resultSetDefinitions> 
 
</design:ResultSets> 
 
</model:DesignValues>]]></xml-property> 
</oda-data-set> 
</data-sets> 
<styles> 
<style name="crosstab-cell" id="4"> 
<property name="borderBottomColor">#CCCCCC</property> 
<property name="borderBottomStyle">solid</property> 
<property name="borderBottomWidth">1pt</property> 
<property name="borderLeftColor">#CCCCCC</property> 
<property name="borderLeftStyle">solid</property> 
<property name="borderLeftWidth">1pt</property> 
<property name="borderRightColor">#CCCCCC</property> 
<property name="borderRightStyle">solid</property> 
<property name="borderRightWidth">1pt</property> 
<property name="borderTopColor">#CCCCCC</property> 
<property name="borderTopStyle">solid</property> 
<property name="borderTopWidth">1pt</property> 
</style> 
<style name="crosstab" id="5"> 
<property name="borderBottomColor">#CCCCCC</property> 
<property name="borderBottomStyle">solid</property> 
<property name="borderBottomWidth">1pt</property> 
<property name="borderLeftColor">#CCCCCC</property> 
<property name="borderLeftStyle">solid</property> 
<property name="borderLeftWidth">1pt</property> 
<property name="borderRightColor">#CCCCCC</property> 
<property name="borderRightStyle">solid</property> 
<property name="borderRightWidth">1pt</property> 
<property name="borderTopColor">#CCCCCC</property> 
<property name="borderTopStyle">solid</property> 
<property name="borderTopWidth">1pt</property> 
</style> 
</styles> 
<page-setup> 
<simple-master-page name="Simple MasterPage" id="2"> 
<page-footer> 
<text id="3"> 
<property name="contentType">html</property> 
<text-property name="content"><![CDATA[<value-of>new Date()</value-of>]]></text-property> 
</text> 
</page-footer> 
</simple-master-page> 
</page-setup> 
<body> 
<table id="8"> 
<property name="width">100%</property> 
<property name="dataSet">Data Set</property> 
<list-property name="boundDataColumns"> 
<structure> 
<property name="name">name</property> 
<expression name="expression">dataSetRow["name"]</expression> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="name">datetime</property> 
<expression name="expression">dataSetRow["datetime"]</expression> 
<property name="dataType">date-time</property> 
</structure> 
</list-property> 
<column id="22"/> 
<column id="23"/> 
<header> 
<row id="9"> 
<cell id="10"> 
<label id="11"> 
<text-property name="text">name</text-property> 
</label> 
</cell> 
<cell id="12"> 
<label id="13"> 
<text-property name="text">datetime</text-property> 
</label> 
</cell> 
</row> 
</header> 
<detail> 
<row id="14"> 
<cell id="15"> 
<data id="16"> 
<property name="resultSetColumn">name</property> 
</data> 
</cell> 
<cell id="17"> 
<data id="18"> 
<property name="resultSetColumn">datetime</property> 
</data> 
</cell> 
</row> 
</detail> 
<footer> 
<row id="19"> 
<cell id="20"/> 
<cell id="21"/> 
</row> 
</footer> 
</table> 
</body> 
</report> 
 
[/xml] 
 
 
 
=========================================================================================== 
mssql_datetime_2_6 
=========================================================================================== 
[xml] 
<?xml version="1.0" encoding="UTF-8"?> 
<report xmlns="http-~~-//www.eclipse.org/birt/2005/design" version="3.2.21" id="1"> 
<property name="comments">Copyright (c) 2007 <<Your Company Name here>></property> 
<property name="createdBy">Eclipse BIRT Designer Version 2.6.0.v20100531 Build <2.6.0.v20100609-1613></property> 
<html-property name="description">Creates a blank report with no predefined content.</html-property> 
<property name="units">in</property> 
<text-property name="displayName">Blank Report</text-property> 
<property name="iconFile">/templates/blank_report.gif</property> 
<property name="layoutPreference">auto layout</property> 
<parameters> 
<scalar-parameter name="NewParameter" id="42"> 
<property name="valueType">static</property> 
<property name="isRequired">true</property> 
<property name="dataType">dateTime</property> 
<property name="distinct">true</property> 
<simple-property-list name="defaultValue"> 
<value type="constant">2010-08-26 15:03:34</value> 
</simple-property-list> 
<property name="paramType">simple</property> 
<property name="controlType">text-box</property> 
<structure name="format"> 
<property name="category">Unformatted</property> 
</structure> 
</scalar-parameter> 
</parameters> 
<data-sources> 
<oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="Data Source" id="6"> 
<list-property name="privateDriverProperties"> 
<ex-property> 
<name>ODAPerspective</name> 
<value>Birt</value> 
</ex-property> 
<ex-property> 
<name>ODAUsageHelperConnection</name> 
<value>Native</value> 
</ex-property> 
</list-property> 
<property name="odaDriverClass">net.sourceforge.jtds.jdbc.Driver</property> 
<property name="odaURL">jdbc:jtds:sqlserver://10.113.58.41/dash1;Instance=;PrepareSQL=0</property> 
<property name="odaUser">sa</property> 
<encrypted-property name="odaPassword" encryptionID="base64">d2VsY29tZQ==</encrypted-property> 
</oda-data-source> 
</data-sources> 
<data-sets> 
<oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" name="Data Set" id="25"> 
<list-property name="columnHints"> 
<structure> 
<property name="columnName">Name</property> 
<text-property name="displayName">Name</text-property> 
<text-property name="heading">Name</text-property> 
<property name="displayLength">50</property> 
<property name="horizontalAlign">justify</property> 
<property name="wordWrap">false</property> 
</structure> 
<structure> 
<property name="columnName">DATETIME</property> 
<text-property name="displayName">DATETIME</text-property> 
<text-property name="heading">DATETIME</text-property> 
<property name="displayLength">23</property> 
<property name="horizontalAlign">justify</property> 
<property name="wordWrap">false</property> 
</structure> 
</list-property> 
<list-property name="parameters"> 
<structure> 
<property name="name">param_1</property> 
<property name="paramName">NewParameter</property> 
<property name="nativeName"></property> 
<property name="dataType">dateTime</property> 
<property name="nativeDataType">1</property> 
<property name="position">1</property> 
<property name="isInput">true</property> 
<property name="isOutput">false</property> 
</structure> 
</list-property> 
<structure name="cachedMetaData"> 
<list-property name="resultSet"> 
<structure> 
<property name="position">1</property> 
<property name="name">Name</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">2</property> 
<property name="name">DATETIME</property> 
<property name="dataType">date-time</property> 
</structure> 
</list-property> 
</structure> 
<property name="dataSource">Data Source</property> 
<list-property name="resultSet"> 
<structure> 
<property name="position">1</property> 
<property name="name">Name</property> 
<property name="nativeName">Name</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">2</property> 
<property name="name">DATETIME</property> 
<property name="nativeName">DATETIME</property> 
<property name="dataType">date-time</property> 
</structure> 
</list-property> 
<xml-property name="queryText"><![CDATA[select * 
from dbo.sriraman_test 
where dbo.sriraman_test.DATETIME = ?]]></xml-property> 
<xml-property name="designerValues"><![CDATA[<?xml version="1.0" encoding="UTF-8"?> 
 
<model:DesignValues xmlns:design="http-~~-//www.eclipse.org/datatools/connectivity/oda/design" xmlns:model="http-~~-//www.eclipse.org/birt/report/model/adapter/odaModel"> 
 
<Version>1.0</Version> 
 
<design:DataSetParameters> 
 
<design:parameterDefinitions> 
 
<design:inOutMode>In</design:inOutMode> 
 
<design:attributes> 
 
<design:name></design:name> 
 
<design:position>1</design:position> 
 
<design:nativeDataTypeCode>1</design:nativeDataTypeCode> 
 
<design:precision>38</design:precision> 
 
<design:scale>0</design:scale> 
 
<design:nullability>Unknown</design:nullability> 
 
</design:attributes> 
 
</design:parameterDefinitions> 
 
</design:DataSetParameters> 
 
<design:ResultSets derivedMetaData="true"> 
 
<design:resultSetDefinitions> 
 
<design:resultSetColumns> 
 
<design:resultColumnDefinitions> 
 
<design:attributes> 
 
<design:name>Name</design:name> 
 
<design:position>1</design:position> 
 
<design:nativeDataTypeCode>12</design:nativeDataTypeCode> 
 
<design:precision>50</design:precision> 
 
<design:scale>0</design:scale> 
 
<design:nullability>Nullable</design:nullability> 
 
<design:uiHints> 
 
<design:displayName>Name</design:displayName> 
 
</design:uiHints> 
 
</design:attributes> 
 
<design:usageHints> 
 
<design:label>Name</design:label> 
 
<design:formattingHints> 
 
<design:displaySize>50</design:displaySize> 
 
</design:formattingHints> 
 
</design:usageHints> 
 
</design:resultColumnDefinitions> 
 
<design:resultColumnDefinitions> 
 
<design:attributes> 
 
<design:name>DATETIME</design:name> 
 
<design:position>2</design:position> 
 
<design:nativeDataTypeCode>93</design:nativeDataTypeCode> 
 
<design:precision>23</design:precision> 
 
<design:scale>3</design:scale> 
 
<design:nullability>Nullable</design:nullability> 
 
<design:uiHints> 
 
<design:displayName>DATETIME</design:displayName> 
 
</design:uiHints> 
 
</design:attributes> 
 
<design:usageHints> 
 
<design:label>DATETIME</design:label> 
 
<design:formattingHints> 
 
<design:displaySize>23</design:displaySize> 
 
</design:formattingHints> 
 
</design:usageHints> 
 
</design:resultColumnDefinitions> 
 
</design:resultSetColumns> 
 
<design:criteria/> 
 
</design:resultSetDefinitions> 
 
</design:ResultSets> 
 
</model:DesignValues>]]></xml-property> 
</oda-data-set> 
</data-sets> 
<styles> 
<style name="crosstab-cell" id="4"> 
<property name="borderBottomColor">#CCCCCC</property> 
<property name="borderBottomStyle">solid</property> 
<property name="borderBottomWidth">1pt</property> 
<property name="borderLeftColor">#CCCCCC</property> 
<property name="borderLeftStyle">solid</property> 
<property name="borderLeftWidth">1pt</property> 
<property name="borderRightColor">#CCCCCC</property> 
<property name="borderRightStyle">solid</property> 
<property name="borderRightWidth">1pt</property> 
<property name="borderTopColor">#CCCCCC</property> 
<property name="borderTopStyle">solid</property> 
<property name="borderTopWidth">1pt</property> 
</style> 
<style name="crosstab" id="5"> 
<property name="borderBottomColor">#CCCCCC</property> 
<property name="borderBottomStyle">solid</property> 
<property name="borderBottomWidth">1pt</property> 
<property name="borderLeftColor">#CCCCCC</property> 
<property name="borderLeftStyle">solid</property> 
<property name="borderLeftWidth">1pt</property> 
<property name="borderRightColor">#CCCCCC</property> 
<property name="borderRightStyle">solid</property> 
<property name="borderRightWidth">1pt</property> 
<property name="borderTopColor">#CCCCCC</property> 
<property name="borderTopStyle">solid</property> 
<property name="borderTopWidth">1pt</property> 
</style> 
</styles> 
<page-setup> 
<simple-master-page name="Simple MasterPage" id="2"> 
<property name="topMargin">1in</property> 
<property name="leftMargin">1.25in</property> 
<property name="bottomMargin">1in</property> 
<property name="rightMargin">1.25in</property> 
<page-footer> 
<text id="3"> 
<property name="contentType">html</property> 
<text-property name="content"><![CDATA[<value-of>new Date()</value-of>]]></text-property> 
</text> 
</page-footer> 
</simple-master-page> 
</page-setup> 
<body> 
<table id="43"> 
<property name="dataSet">Data Set</property> 
<list-property name="boundDataColumns"> 
<structure> 
<property name="name">Name</property> 
<text-property name="displayName">Name</text-property> 
<expression name="expression" type="javascript">dataSetRow["Name"]</expression> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="name">DATETIME</property> 
<text-property name="displayName">DATETIME</text-property> 
<expression name="expression" type="javascript">dataSetRow["DATETIME"]</expression> 
<property name="dataType">date-time</property> 
</structure> 
</list-property> 
<column id="57"/> 
<column id="58"/> 
<header> 
<row id="44"> 
<cell id="45"> 
<label id="46"> 
<text-property name="text">Name</text-property> 
</label> 
</cell> 
<cell id="47"> 
<label id="48"> 
<text-property name="text">DATETIME</text-property> 
</label> 
</cell> 
</row> 
</header> 
<detail> 
<row id="49"> 
<cell id="50"> 
<data id="51"> 
<property name="whiteSpace">nowrap</property> 
<property name="resultSetColumn">Name</property> 
</data> 
</cell> 
<cell id="52"> 
<data id="53"> 
<property name="whiteSpace">nowrap</property> 
<property name="resultSetColumn">DATETIME</property> 
</data> 
</cell> 
</row> 
</detail> 
<footer> 
<row id="54"> 
<cell id="55"/> 
<cell id="56"/> 
</row> 
</footer> 
</table> 
</body> 
</report> 
 
[/xml] 
 
=========================================================================================== 
oracle_timestamp_2_2_1 
=========================================================================================== 
[xml] 
<?xml version="1.0" encoding="UTF-8"?> 
<report xmlns="http-~~-//www.eclipse.org/birt/2005/design" version="3.2.15" id="1"> 
<property name="createdBy">Eclipse BIRT Designer Version 2.2.1.r221_v20070924 Build <2.2.0.v20070924-1550></property> 
<property name="units">in</property> 
<property name="comments">Copyright (c) 2007 <<Your Company Name here>></property> 
<html-property name="description">Creates a blank report with no predefined content.</html-property> 
<text-property name="displayName">Blank Report</text-property> 
<property name="iconFile">/templates/blank_report.gif</property> 
<parameters> 
<scalar-parameter name="param_1" id="24"> 
<property name="valueType">static</property> 
<property name="dataType">dateTime</property> 
<property name="paramType">simple</property> 
<property name="isRequired">true</property> 
<property name="controlType">text-box</property> 
<property name="defaultValue">09/01/2010 03.59.00 PM</property> 
<property name="distinct">true</property> 
<structure name="format"> 
<property name="category">Unformatted</property> 
</structure> 
</scalar-parameter> 
</parameters> 
<data-sources> 
<oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="Data Source" id="6"> 
<text-property name="displayName"></text-property> 
<list-property name="privateDriverProperties"> 
<ex-property> 
<name>ODAPerspective</name> 
<value>Birt</value> 
</ex-property> 
<ex-property> 
<name>ODAUsageHelperConnection</name> 
<value>Native</value> 
</ex-property> 
</list-property> 
<property name="odaDriverClass">oracle.jdbc.OracleDriver</property> 
<property name="odaURL">jdbc:oracle:thin:@10.113.57.248:1521:sriraman</property> 
<property name="odaUser">dash1</property> 
<encrypted-property name="odaPassword" encryptionID="base64">ZGFzaDE=</encrypted-property> 
</oda-data-source> 
</data-sources> 
<data-sets> 
<oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" name="Data Set" id="7"> 
<structure name="cachedMetaData"/> 
<property name="dataSource">Data Source</property> 
<list-property name="parameters"> 
<structure> 
<property name="name">param_1</property> 
<property name="paramName">param_1</property> 
<property name="nativeName"></property> 
<property name="dataType">dateTime</property> 
<property name="nativeDataType">1</property> 
<property name="position">1</property> 
<property name="isOptional">false</property> 
<property name="allowNull">false</property> 
<property name="isInput">true</property> 
<property name="isOutput">false</property> 
</structure> 
</list-property> 
<property name="queryText">select * 
from DASH1.SRIRAMAN_TEST 
where DASH1.SRIRAMAN_TEST.DATETIME = ?</property> 
<xml-property name="designerValues"><![CDATA[<?xml version="1.0" encoding="UTF-8"?> 
 
<model:DesignValues xmlns:design="http-~~-//www.eclipse.org/datatools/connectivity/oda/design" xmlns:model="http-~~-//www.eclipse.org/birt/report/model/adapter/odaModel"> 
 
<Version>1.0</Version> 
 
<design:DataSetParameters> 
 
<design:parameterDefinitions> 
 
<design:inOutMode>In</design:inOutMode> 
 
<design:attributes> 
 
<design:name></design:name> 
 
<design:position>1</design:position> 
 
<design:nativeDataTypeCode>1</design:nativeDataTypeCode> 
 
<design:nullability>Unknown</design:nullability> 
 
<design:uiHints/> 
 
</design:attributes> 
 
<design:inputAttributes> 
 
<design:elementAttributes> 
 
<design:defaultScalarValue>09/01/2010 03.59.00 PM</design:defaultScalarValue> 
 
<design:optional>false</design:optional> 
 
<design:masksValue>false</design:masksValue> 
 
<design:uiHints> 
 
<design:promptStyle>TextField</design:promptStyle> 
 
</design:uiHints> 
 
</design:elementAttributes> 
 
</design:inputAttributes> 
 
</design:parameterDefinitions> 
 
</design:DataSetParameters> 
 
<design:ResultSets derivedMetaData="true"> 
 
<design:resultSetDefinitions> 
 
<design:resultSetColumns> 
 
<design:resultColumnDefinitions> 
 
<design:attributes> 
 
<design:name>name</design:name> 
 
<design:position>1</design:position> 
 
<design:nativeDataTypeCode>12</design:nativeDataTypeCode> 
 
<design:precision>4000</design:precision> 
 
<design:scale>0</design:scale> 
 
<design:nullability>Nullable</design:nullability> 
 
<design:uiHints> 
 
<design:displayName>name</design:displayName> 
 
</design:uiHints> 
 
</design:attributes> 
 
<design:usageHints> 
 
<design:label>name</design:label> 
 
<design:formattingHints> 
 
<design:displaySize>4000</design:displaySize> 
 
</design:formattingHints> 
 
</design:usageHints> 
 
</design:resultColumnDefinitions> 
 
<design:resultColumnDefinitions> 
 
<design:attributes> 
 
<design:name>datetime</design:name> 
 
<design:position>2</design:position> 
 
<design:nativeDataTypeCode>93</design:nativeDataTypeCode> 
 
<design:precision>0</design:precision> 
 
<design:scale>6</design:scale> 
 
<design:nullability>Nullable</design:nullability> 
 
<design:uiHints> 
 
<design:displayName>datetime</design:displayName> 
 
</design:uiHints> 
 
</design:attributes> 
 
<design:usageHints> 
 
<design:label>datetime</design:label> 
 
<design:formattingHints> 
 
<design:displaySize>11</design:displaySize> 
 
</design:formattingHints> 
 
</design:usageHints> 
 
</design:resultColumnDefinitions> 
 
</design:resultSetColumns> 
 
</design:resultSetDefinitions> 
 
</design:ResultSets> 
 
</model:DesignValues>]]></xml-property> 
</oda-data-set> 
</data-sets> 
<styles> 
<style name="crosstab-cell" id="4"> 
<property name="borderBottomColor">#CCCCCC</property> 
<property name="borderBottomStyle">solid</property> 
<property name="borderBottomWidth">1pt</property> 
<property name="borderLeftColor">#CCCCCC</property> 
<property name="borderLeftStyle">solid</property> 
<property name="borderLeftWidth">1pt</property> 
<property name="borderRightColor">#CCCCCC</property> 
<property name="borderRightStyle">solid</property> 
<property name="borderRightWidth">1pt</property> 
<property name="borderTopColor">#CCCCCC</property> 
<property name="borderTopStyle">solid</property> 
<property name="borderTopWidth">1pt</property> 
</style> 
<style name="crosstab" id="5"> 
<property name="borderBottomColor">#CCCCCC</property> 
<property name="borderBottomStyle">solid</property> 
<property name="borderBottomWidth">1pt</property> 
<property name="borderLeftColor">#CCCCCC</property> 
<property name="borderLeftStyle">solid</property> 
<property name="borderLeftWidth">1pt</property> 
<property name="borderRightColor">#CCCCCC</property> 
<property name="borderRightStyle">solid</property> 
<property name="borderRightWidth">1pt</property> 
<property name="borderTopColor">#CCCCCC</property> 
<property name="borderTopStyle">solid</property> 
<property name="borderTopWidth">1pt</property> 
</style> 
</styles> 
<page-setup> 
<simple-master-page name="Simple MasterPage" id="2"> 
<page-footer> 
<text id="3"> 
<property name="contentType">html</property> 
<text-property name="content"><![CDATA[<value-of>new Date()</value-of>]]></text-property> 
</text> 
</page-footer> 
</simple-master-page> 
</page-setup> 
<body> 
<table id="8"> 
<property name="width">100%</property> 
<property name="dataSet">Data Set</property> 
<list-property name="boundDataColumns"> 
<structure> 
<property name="name">name</property> 
<expression name="expression">dataSetRow["name"]</expression> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="name">datetime</property> 
<expression name="expression">dataSetRow["datetime"]</expression> 
<property name="dataType">date-time</property> 
</structure> 
</list-property> 
<column id="22"/> 
<column id="23"/> 
<header> 
<row id="9"> 
<cell id="10"> 
<label id="11"> 
<text-property name="text">name</text-property> 
</label> 
</cell> 
<cell id="12"> 
<label id="13"> 
<text-property name="text">datetime</text-property> 
</label> 
</cell> 
</row> 
</header> 
<detail> 
<row id="14"> 
<cell id="15"> 
<data id="16"> 
<property name="resultSetColumn">name</property> 
</data> 
</cell> 
<cell id="17"> 
<data id="18"> 
<property name="resultSetColumn">datetime</property> 
</data> 
</cell> 
</row> 
</detail> 
<footer> 
<row id="19"> 
<cell id="20"/> 
<cell id="21"/> 
</row> 
</footer> 
</table> 
</body> 
</report> 
 
[/xml] 
 
=========================================================================================== 
oracle_timestamp_2_6 
=========================================================================================== 
[xml] 
<?xml version="1.0" encoding="UTF-8"?> 
<report xmlns="http-~~-//www.eclipse.org/birt/2005/design" version="3.2.21" id="1"> 
<property name="createdBy">Eclipse BIRT Designer Version 2.6.0.v20100531 Build <2.6.0.v20100609-1613></property> 
<property name="units">in</property> 
<property name="iconFile">/templates/blank_report.gif</property> 
<property name="bidiLayoutOrientation">ltr</property> 
<property name="imageDPI">96</property> 
<parameters> 
<scalar-parameter name="NewParameter2" id="25"> 
<property name="valueType">static</property> 
<property name="dataType">dateTime</property> 
<property name="distinct">true</property> 
<simple-property-list name="defaultValue"> 
<value type="constant">2010-09-01 15:59:00</value> 
</simple-property-list> 
<property name="paramType">simple</property> 
<property name="controlType">text-box</property> 
<structure name="format"> 
<property name="category">Unformatted</property> 
</structure> 
</scalar-parameter> 
</parameters> 
<data-sources> 
<oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="Data Source" id="7"> 
<list-property name="privateDriverProperties"> 
<ex-property> 
<name>contentBidiFormatStr</name> 
<value>ILYNN</value> 
</ex-property> 
<ex-property> 
<name>metadataBidiFormatStr</name> 
<value>ILYNN</value> 
</ex-property> 
</list-property> 
<property name="odaDriverClass">oracle.jdbc.OracleDriver</property> 
<property name="odaURL">jdbc:oracle:thin:@10.113.57.248:1521:sriraman</property> 
<property name="odaUser">dash1</property> 
<encrypted-property name="odaPassword" encryptionID="base64">ZGFzaDE=</encrypted-property> 
</oda-data-source> 
</data-sources> 
<data-sets> 
<oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" name="Data Set" id="8"> 
<list-property name="parameters"> 
<structure> 
<property name="name">param_1</property> 
<property name="paramName">NewParameter2</property> 
<property name="dataType">dateTime</property> 
<property name="position">1</property> 
<property name="isInput">true</property> 
<property name="isOutput">false</property> 
</structure> 
</list-property> 
<structure name="cachedMetaData"> 
<list-property name="resultSet"> 
<structure> 
<property name="position">1</property> 
<property name="name">NAME</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">2</property> 
<property name="name">DATETIME</property> 
<property name="dataType">date-time</property> 
</structure> 
</list-property> 
</structure> 
<property name="dataSource">Data Source</property> 
<list-property name="resultSet"> 
<structure> 
<property name="position">1</property> 
<property name="name">NAME</property> 
<property name="nativeName">NAME</property> 
<property name="dataType">string</property> 
</structure> 
<structure> 
<property name="position">2</property> 
<property name="name">DATETIME</property> 
<property name="nativeName">DATETIME</property> 
<property name="dataType">date-time</property> 
</structure> 
</list-property> 
<xml-property name="queryText"><![CDATA[select * 
from DASH1.SRIRAMAN_TEST 
where SRIRAMAN_TEST.DATETIME = ?]]></xml-property> 
<xml-property name="designerValues"><![CDATA[<?xml version="1.0" encoding="UTF-8"?> 
 
<model:DesignValues xmlns:design="http-~~-//www.eclipse.org/datatools/connectivity/oda/design" xmlns:model="http-~~-//www.eclipse.org/birt/report/model/adapter/odaModel"> 
 
<Version>1.0</Version> 
 
<design:ResultSets derivedMetaData="true"> 
 
<design:resultSetDefinitions> 
 
<design:resultSetColumns> 
 
<design:resultColumnDefinitions> 
 
<design:attributes> 
 
<design:name>NAME</design:name> 
 
<design:position>1</design:position> 
 
<design:nativeDataTypeCode>12</design:nativeDataTypeCode> 
 
<design:precision>4000</design:precision> 
 
<design:scale>0</design:scale> 
 
<design:nullability>Nullable</design:nullability> 
 
<design:uiHints> 
 
<design:displayName>NAME</design:displayName> 
 
</design:uiHints> 
 
</design:attributes> 
 
<design:usageHints> 
 
<design:label>NAME</design:label> 
 
<design:formattingHints> 
 
<design:displaySize>4000</design:displaySize> 
 
</design:formattingHints> 
 
</design:usageHints> 
 
</design:resultColumnDefinitions> 
 
<design:resultColumnDefinitions> 
 
<design:attributes> 
 
<design:name>DATETIME</design:name> 
 
<design:position>2</design:position> 
 
<design:nativeDataTypeCode>93</design:nativeDataTypeCode> 
 
<design:precision>0</design:precision> 
 
<design:scale>6</design:scale> 
 
<design:nullability>Nullable</design:nullability> 
 
<design:uiHints> 
 
<design:displayName>DATETIME</design:displayName> 
 
</design:uiHints> 
 
</design:attributes> 
 
<design:usageHints> 
 
<design:label>DATETIME</design:label> 
 
<design:formattingHints> 
 
<design:displaySize>11</design:displaySize> 
 
</design:formattingHints> 
 
</design:usageHints> 
 
</design:resultColumnDefinitions> 
 
</design:resultSetColumns&gt

Find more posts tagged with

Comments

Sriraman

Help required guys... need to resolve this issue urgently...

johnw

When working with dates in BIRT, make sure they are in the YYYY-MM-DD HH:MM:SS.SSS format. to convert them if they are strings, use the Packages.java.text.SimpleDateFormat object. It would look something like: 
 
var formater = new Packages.java.text.SimpleDateFormat("YYYY-MM-DD HH:MM:SS.SSS"); 
 
params["dateParam"] = formater.format(row["myDate"]); 
 
Or something to that effect. In your report, your using the "08/26/2010 01:03:34 PM" format, which will not work. 
 
John 
 
<blockquote class='ipsBlockquote' data-author="'Sriraman'" data-cid="68143" data-time="1283749117" data-date="05 September 2010 - 09:58 PM">
Help required guys... need to resolve this issue urgently... </blockquote>

kkapera

Did you fix your problem. I am having the same or similar issue. I am connecting to an Oracle 11.2 instance. Using a Date Time parameter and to_timestamp I get errors. I had to convert the parameters to string and then to to_date for date comparisons making sure that the format string matches the string entered (yyyy-mm-dd hh24:mi:ss).