The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
DateTime issue
Posted: 13 Jul 07 12:36 PM
|
Hello,
I am a newbie on SalesLogix and I run into some problems width DateTime fields. Here is my problem:
I run this query inside SalesLogixs Administrator Tool.
SELECT t.c_trainingid, t.SHIP_DATE, e.ACTION_DATE, DATEDIFF("hh", t.SHIP_DATE, e.ACTION_DATE) AS DateDiffChecking
FROM C_TRAINING t INNER JOIN eii_sofst e ON t.so_id = e.so_id
WHERE t.so_id IS NOT NULL
AND c_trainingid = 'QPMCPA02LWL9'
AND Convert(Char(10), t.SHIP_DATE, 101) <> Convert(Char(10), e.action_date, 101)
c_trainingid, SHIP_DATE, ACTION_DATE, DateDiffChecking
-------------------------------------------------------------------
'QPMCPA02LWL9','26-Mar-2007 20:00:00','19-Mar-2007 20:00:00',-164
'QPMCPA02LWL9','26-Mar-2007 20:00:00','19-Mar-2007 20:00:00',-164
'QPMCPA02LWL9','26-Mar-2007 20:00:00','19-Mar-2007 20:00:00',-164
'QPMCPA02LWL9','26-Mar-2007 20:00:00','19-Mar-2007 20:00:00',-164
'QPMCPA02LWL9','26-Mar-2007 20:00:00','19-Mar-2007 20:00:00',-164
'QPMCPA02LWL9','26-Mar-2007 20:00:00','19-Mar-2007 20:00:00',-164
'QPMCPA02LWL9','26-Mar-2007 20:00:00','19-Mar-2007 20:00:00',-164
'QPMCPA02LWL9','26-Mar-2007 20:00:00','19-Mar-2007 20:00:00',-164
'QPMCPA02LWL9','26-Mar-2007 20:00:00','26-Mar-2007 20:00:00',4
'QPMCPA02LWL9','26-Mar-2007 20:00:00','19-Mar-2007 20:00:00',-164
'QPMCPA02LWL9','26-Mar-2007 20:00:00','19-Mar-2007 20:00:00',-164
As you can see in line# 9
'QPMCPA02LWL9','26-Mar-2007 20:00:00','26-Mar-2007 20:00:00',4
Why 4 hours difference?
I need to compare these 2 date fields in a VS 2005 application using ADO.NET 2.0. I am using an SLX OLEDB provider to connect to the source.
What I checked:
1)
eii_sosft is a view with eii_sosft.ActionDate = ‘Date’ as field type. (not DateTime)
c_training is a table with c_trainig.SHIP_DATE = ‘Date’ as field type.
2) Constraint on SECTABLEDEFS.DATETIMETYPE field
DATETIMETYPE TABLENAME FIELDNAME
------------ -------------------------------- --------------------------------
D C_TRAINING SHIP_DATE
D EII_SOFST ACTION_DATE
Does anybody have any suggestions?
Thanks !
 |
|
|
|
Re: DateTime issue
Posted: 13 Jul 07 4:53 PM
|
The SlxProvider converts datetime values to GMT 0 values before storing them in the database. The offset used is based on time timezone of the system that is writing ot the database.
When datatime values are read back the SlxProvider does the reverse. |
|
|
|
Re: DateTime issue
Posted: 14 Jul 07 8:19 AM
|
Frank,
There is NO conversion on a field that is a "D" type in SECTABLEDEFS.DATETIMETYPE - It's a "blind" pass-thru both ways. SO IF there is a time.. it passes AS IS.
--
rjl |
|
|
|
Re: DateTime issue
Posted: 14 Jul 07 8:34 AM
|
RJ,
You are correct. Setting the datetimetype to D effectively makes the SlxProvider treat the field as a DATE field vs a DATETIME field. |
|
|
|
Re: DateTime issue
Posted: 15 Jul 07 2:16 PM
|
Frank and RJ and Anyone else for that matter ,
If you access the data directly through the provider from an external app would get both date and time because it is still stored as date time in sql server? or is the provider going to the database and doing a lookup and making that change based on D,U,T in sectabledef.DateTimetype? Thanks ML |
|
|
|
Re: DateTime issue
Posted: 15 Jul 07 5:39 PM
|
D=Date: used to store dates. provider ignores time and writes/reads date as is.
T=Time: used to store timestamps. provider ignores date and writes/reads time as is.
All other values undergo GMT offsetting on the way into and outof the database. The GMT offsetting is based on the timezone of the client system that is reading/writing the data.
So if you are setting in EST and the current time on you PC is 2-Jan-2005 at 5:04:30am (ie 10:04:30am GMT) and you write the current time into a datetime field the provider stores "20050102 100430" (2-Jan-2005 at 10:04:30am).
If someone in GMT reads it back via the provider they get 20050102 100430 (2-Jan-2005 at 10:04:30am).
If you read it back via the provider you get 20050102 050430 (2-Jan-2005 at 5:04:30am) because you are in EST (which is GMT-5).
If you set the timezone on you PC to PST and read it back via the provider you get 20050102 020430 (2-Jan-2005 at 2:04:30am).
If you query the DB directly you get the RAW data.
Does this make sense? |
|
|
|
Re: DateTime issue
Posted: 15 Jul 07 6:15 PM
|
| What about on an export to excel or some other 3rd party app? Thanks ML |
|
|
|
Re: DateTime issue
Posted: 15 Jul 07 6:39 PM
|
If the function or 3rd party app takls to the database through the Slx Client and/or Slx Provider, then the SLX Provider will adjust the dates.
This is how the pieces fit for MSSQL...
MSSQL Server DB
^
|
V
MS OLEDB Provider
^
|
V
SLX OLEDB Provider
^
|
V
SalesLogix.exe
|
|
|
|
Re: DateTime issue
Posted: 16 Jul 07 8:05 AM
|
Hi all,
Thank you for your help.
Still my questions stays open. I am looking at RAW data in SLX, using SalesLogix Administrator Tool and I see this row:
c_trainingid, SHIP_DATE, ACTION_DATE, DateDiffChecking
------------------------------------------------------------------------------------------------------------------
'QPMCPA02LWL9', '26-Mar-2007 20:00:00', '26-Mar-2007 20:00:00', 4
As you can see both dates are the same (date and hour) amd whan I am comparing them I get 4 hours difference. I don't understand where this difference is coming from.
One more thing:
If I run the same query through SQL2005 I get different data: (just ACTION_DATE gets 'rounded up')
c_trainingid SHIP_DATE ACTION_DATE DateDiffChecking
------------ ----------------------- ----------------------- -------------------------------------------------------
QPMCPA02LWL9 2007-03-26 20:00:00.000 2007-03-27 00:00:00.000 4
Am I missing some settings on SLX side?
Thanks again for all your help! |
|
|
|
Re: DateTime issue
Posted: 16 Jul 07 12:48 PM
|
What does the SalesLogix DB Manager say the data type is for SHIP_DATE, and ACTION_DATE?
Better yet what does SetTableDEFs say about these fields?
|
|
|
| |
|
Re: DateTime issue
Posted: 16 Jul 07 5:49 PM
|
| So everything is set properly and the proper values are displayed for other rows from the underlying tables. Could there might be something different with the information that is stored in this one row, that we can see, but is confusing the provider? Very strange. Can you try rewriting the row in question, or deleting it and re-adding it? |
|
|
|
Re: DateTime issue
Posted: 17 Jul 07 9:22 AM
|
Frank,
All the rows have the same problem. 4 hours difference which I don't understand where is coming from. I just listed the most obvious one, where the dates were “equal”, but when I compare them it gives me 4 hours difference.
I will try and delete and rewrite that line, but I don’t hope much.
Actually I will create brand new tables just to play with the dates/times see if I can recreate the problem on different tables.
Thanks for your help.
|
|
|
|
Re: DateTime issue
Posted: 17 Jul 07 12:20 PM
|
| You mentioned that one of the table came from a view. Perhaps that is confusing the SlxProvider. |
|
|
|
Re: DateTime issue
Posted: 18 Jul 07 5:13 PM
|
It sure seems that the provider (this is a v7.0.1 system by the way and "supports" Slx enabled SQL views) has a problem here.
--
rjl |
|
|
|
Re: DateTime issue
Posted: 24 Jul 07 2:43 PM
|
To clarify - if you are using the admin execute sql feature, you are NOT seeing RAW data. This is data as it is parsed through the SLXOLEDB provider. To see RAW data you must use a tool such as SQL Query Analyzer or SQL Management Studio. If you look at the data with these tools you will see the dates are four hours off.
As RJL states, we are having issues with SQL Views and SalesLogix incorrectly applying GMT offsets eventhough we have indicated we want the field to be date only.
Timmus |
|
|
|
Re: DateTime issue
Posted: 25 Jul 07 11:52 AM
|
Timmus,
I thought is the other way around...Outside the provider is x hours off. 
Thanks for clarification. |
|
|
|
Re: DateTime issue
Posted: 26 Jul 07 2:20 PM
|
Veronka,
I apologize for the miscommunication; this is a confusing topic. In order to see raw data (data as it is according to the RDBMS) you can NOT use the SLXOLEDB provider. The provider adjusts dates for GMT, adds row level security, translates calculated fields, etc.
In your case, you are inadvertently mixing the two. You are going through the provider so it is adjusting the dates for GMT when you simply include the columns in the select list. However, when you use a SQL Function such as DateDiff, the provider is not adjusting the values within that function. So you are not comparing apples to apples. Does that make sense?
Based on the original query, I have pointed out what value are adjusted for GMT and which are not:
SELECT
t.c_trainingid,
t.SHIP_DATE, -- Adjusted
e.ACTION_DATE, -- Adjusted
DATEDIFF("hh", t.SHIP_DATE, e.ACTION_DATE) AS DateDiffChecking -- NOT Adjusted
FROM
C_TRAINING t INNER JOIN eii_sofst e ON t.so_id = e.so_id
WHERE
t.so_id IS NOT NULL
AND c_trainingid = 'QPMCPA02LWL9'
AND
Convert(Char(10), t.SHIP_DATE, 101) -- NOT Adjusted
<>
Convert(Char(10), e.action_date, 101) -- NOT Adjusted
I hope that helps!
Timmus
|
|
|
|
Re: DateTime issue
Posted: 26 Jul 07 2:31 PM
|
Yes it does !
Thank you, thank you, thank you !!!
Pesky DateDiff !!!
|
|
|
|
Re: DateTime issue
Posted: 26 Jul 07 4:33 PM
|
This sort of reply is why I hang out here. Thanks Timmus, that makes things much clearer to me.
 |
|
|
|
Re: DateTime issue
Posted: 27 Jul 07 5:19 PM
|
You are welcome! I hang out here because it is great to see folks benefit from SalesLogix. It's a great product but is often undervalued/underused because it requires fairly deep knowledge to leverage its power.
Timmus |
|
|
|