Date issue in summary extract query

[Former Member]

Hi,

We have implemented Sourcing 7.0.01. I have written a query where i am using the auction event table to get created at date column for auction event (CREATED_AT) and the truncated created at date for auction (<%SCHEMA%>.FCI_DATETRUNC(T1.CREATED_AT)) as the query output. The first column is giving "6/18/2012 11:01 AM" as output and the truncated created at date column gives "6/17/2012" as output.

      Another issue is that if i am trying to print the created at date for auction event as type date time it is giving me "6/18/2012 11:01 AM" as output but when i am printing the same date as type date it is giving "6/17/2012" as output.

The filter parameter that  I have given for both the date parameter “Time period beginning at” and “Time period ending at” is same as “6/17/2012” and these parameter are compared with the created at date of auction event . The query statement is given below:

SELECT <%RESULTS%> FROM <%SCHEMA%>.FCI_AUCT_AUCTION_EVENT T1 LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_INTERNAL_CAT T3 ON T1.INTERNAL_CAT_OBJECT_ID = T3.OBJECTID <%MS_JOIN%>, <%SCHEMA%>.FCI_AUCT_DOCTYPE T2 WHERE T1.CONTEXTID=<%CONTEXT(doc.auction.auctionevent)%> AND T1.IS_TEMPLATE = 0 AND T1.INACTIVE= 0 AND
T1.CREATED_AT >= ? AND T1.CREATED_AT < <%SCHEMA %>.FCI_DATEADD(?,1,16) AND T2.OBJECTID = T1.DOC_TYPE_OBJECT_ID <%ORDERBY%>

If the created at date is “6/18/2012 11:01AM” how come that auction is being listed in the output with both start and end date as “6/17/2012”?

The screenshot for output is attached below.

      I also has a question regarding what is the default time set for a date when a parameter is being used as a filter parameter in the query and it's type is date.Is it related to the default time set in the database?

If the filter parameter mentioned above in the query is of type date time the default time is “1:30 A:M” and then for the same date “6/17/12” i am not getting any output. This default time is set differently in different servers. I guess there is a default time which is being set for parameters when they are used as date as well and that may be  affecting  the query output.

The screenshot for the same is attached below.

    I wanted to know how this FCI_DATETRUNC function works and why it is shifting the truncated date one day before the original created at date and also how the default time is set for a date for the problem mentioned in the attachment and if there is any alternate solution.

Thanks and Regards,

Vijay.

[vikram_shanmugasundaram]

Hi Vijay,

FCI_DATETRUNC function basically calls the SQL TRUNC function, so I don’t believe it is causing the behavior you are seeing. More likely it is timezone related. There are various timezones  like the app server timezone,  database server timezone, OS timezone of the database server and user’s timezone that come into play here. In your screenshot, the “Created At as Date Time” column shows the date time of the CREATED_AT column which is basically the converted date time based on the timezone setup on the user account of the user executing the query. Whereas the next two columns are Date column type so the system just displays date part of the raw data. So for example, let’s say the database server is in EST. The MA is created at 11 PM EST on 06/17/2012. Now, if you execute the query and if your timezone(on the user accout settings) is set to IST, then you would see 06/18/2012 for a Date column.

Hope this clarifies.

Regards,

Vikram