MII Sql Query Template - Time in the Data is advanced by 1 Hr automatically

[Former Member]

Hi All,

I have an entry in MII DataBase(SQL Server 2005) with start and End date fields as "2009-05-01 00:00:00" and "2009-06-01 00:00:00". when i read this record in query template the same is getting displayed as "2009-05-01 00:00:00" and "2009-06-01 01:00:00".

I wonder is it applying any daylight saving calculations and automatically advancing by 1 hour?. The MII server is in WET Timezone.

If i read the same entry in SQL studio, it displays correct data only?.

Thanks,

Gowtham K.

[Former Member]

with more details

Key Start_date End_date

1 2009-05-31 00:00:00 2009-06-01 00:00:00

2. 2009-06-01 00:00:00 2009-07-01 00:00:00

.....

........

........

.........

Tha data is like this in the table. But i read this table with matching Key 1 &2. It disply in the following way..

Key Start_date End_date

1 2009-05-31 00:00:00 2009-06-01 01:00:00

2 2009-06-01 01:00:00 2009-07-01 00:00:00

The End_date in the First record and the Start date in the second record are advanced by 1 Hr.

Thanks,

Gowtham K.

[Former Member]

If I am correct, it happened only for '2009-06-01 00:00:00" ? "2009-07-01 00:00:00" is displayed correctly? What about rest of the data...are those ok?

[Former Member]

Yes the rest of the data is perfectly ok. And i tested this on other MII server which is in CST timezone. And i created a record with 2009-03-08 02:00:00 time. It is advanced by 1Hr when MII displayed it in Query template. I really wonder why we need this kind of behaviour in query template. Is there any setting or Parameter in query template where i can overwrite the behaviour.

just for information we are on MII 12.0.8(build 19)

Thanks,

Gowtham K.

[jcgood25]

Where are you seeing this? WB internal panel, html results in browser, xml results in browser, applet?

[Former Member]

Hi Jeremy,

I saw this in WB internal panel, html and XML results.

Thanks,

Gowtham K.

[Former Member]

just to add, both in browser and inside the transaction when we call this sql query. I didn't check it in applet.

Thanks,

Gowtham K.

[jcgood25]

What is your query?

[Former Member]

select * from <tablename> where Sent_status = 0.

Thanks,

Gowtham K.

[jcgood25]

What are the Data Server summary settings for your connection?

[Former Member]

Name Value

Connector IDBC

ConnectorID

ConnectorType SQL

DatePrefix '

DateSuffix '

DaysRetention 7

Description ---

Enabled T

InitCommand

InternalDateFormat yyyy-MM-dd HH:mm:ss

JDBCDriver com.microsoft.sqlserver.jdbc.SQLServerDriver

MaxRetryCount 5

Name MII

PoolMax 100

PoolSize 1

RetryInterval 60000

ServerPackage com.sap.xmii.Illuminator.connectors.IDBC

ServerURL jdbc:sqlserver://<server>:1433;databaseName=X2D

Timeout 15

UseCount 256

UserName SAPX2DDB

ValidationQuery SELECT GETDATE()

WaitTime 30

Thanks,

Gowtham K.

Edited by: Gowtham Kuchipudi on Mar 5, 2010 12:16 AM

[jcgood25]

This issue is quite strange - I assume when you test the query in text/xml format that the SQLDataType=93 in the Column definition?

Not that you should have to do it, but did you try making a select statement to cast/convert the datetime column into a string to see if the expected time is any different than when it is treated as a datetime?

[Former Member]

Hi Jeremy,

It worked. When we convert the date to string format, it displayed the correct time. But just wondering is there any patch or something we can apply to solve this instead of converting date to string in all the query templates.

Thanks,

Gowtham K.

[jcgood25]

Your best bet would be to log a support ticket at this point.

Include your complete version/build numbers for MII, sqlserver version, a description of your landscape (location of MII in relation to the database server), the data server summary settings, and a snapshot of the database table design.