XL 2K3 External Data Query, Cell Formatting changes on some desktops

[brookwood]

I've written SQL for an external data query and return the data to the worksheet. The external query pulls data from an IBM DB2 database. I've formatted the worksheet including formatting cells to render acceptable standard MS XL date formating rather than the yyyy-mm-dd 00:00:00.000000 formating from DB2.

The date formating works fine on many users desktops. However, on some desktops, I get this: 2008-09-26-16.20.57.000000 (as an example).

All of the users use XL 2K3.
I've protected the workbook structure.
I've verified that the regional settings are the same on pc with favorable results vs. those with unfavorable results.
All users OS is Win XP.

Any help or suggestions is greatly appreciated. Honestly, this is about to drive me loco.

Brookwood

 

[kjv1611]

Have you checked the original data to be sure that the data being pulled is not inputed incorrectly in the system of record? Is it possible for someone to enter a text value as apposed to date/numeric value in the system of record?

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

inputed -

--

"If to err is human, then I must be some kind of human!" -Me

 

[brookwood]

Yes, the system of record is Siebel 7.5.3 and the field is locked down to D_Type Date.

It just blows my mind that it is formatted correctly on over half of the users opening and refreshing the workbook, and for others, it changes the formatting when the workbook refreshes the query.

 

[SkipVought]

On this PC, what happens when you change any Cell Format in the Date column to GENERAL?

Please respond with specific before and after display values.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[brookwood]

Here is the display value before: 2008-04-28-08.56.07.000000 The cell formatting is Number(tab), Category(list): Date, Type(list) 3/14/01.

Here is the display value after: 2008-04-28-08.56.07.000000
The cell formatting is Number (tab), Category(list): General.

 

[SkipVought]

Then you have a STRING and not a DATE.

Enter a 1 (ONE) in an empty cell

COPY that cell

Select the data in the DATE column

Edit > Paste Special -- MULTIPLY

Format column with desired date format.

VOLA y'all!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[brookwood]

That suggestion did not work.

Here is what I'm getting on a worksheet from a user's pc that does render the desired results:

Format Cells > Number > Category: Date > Type: 3/14/01 Display value: 9/30/09 Formula Bar value: 09/30/2009 1:18:14 PM

Format Cells > Number > Category: General Display value: 40086.55433 Formula Bar value: 40086.5543287037

Here is what I'm getting on a worksheet from a user's pc that does not render the desired results:

Format Cells > Number > Category: Date > Type: 3/14/01 Display value: 2008-04-28-08.56.07.000000 Formula Bar: 2008-04-28-08.56.07.000000

Format Cells > Number > Category: General Display value: 2008-04-28-08.56.07.000000 Formula Bar: 2008-04-28-08.56.07.000000

When I look at the data in the MS Query results, the data looks like this format: yyyy-mm-dd hh:mm:ss.ssssss

When the MS Query results return to the worksheet on the user's desktops that do not render the desired results, it is returning in the data in a format of yyyy-mm-dd.hh.mm.ssssss Thus, it is reading it as a string.

Is there some setting on these desktops that is causing MS Query to return the data like this, or some other setting I'm missing?

I don't understand why it would return the data as a date format on some and a string on others.

 

[SkipVought]

Format Cells > Number > Category: Date > Type: 3/14/01 Display value: 2008-04-28-08.56.07.000000 Formula Bar: 2008-04-28-08.56.07.000000

Format Cells > Number > Category: General Display value: 2008-04-28[red]-08.56.07.000000[/red] Formula Bar: 2008-04-28-08.56.07.000000

This tells me that you do not have a date, you have a TEXT!!!

And the PROBLEM is [red]this part[/red] within the string.

Modify your query to return the INTEGER part of the timestamp only. Or use Data > Text to columns to parse and remove [red]this part[/red].

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[brookwood]

Ok, so that explains why I'm getting two different results running the exact same query with the exact same formatting, on the exact same version and SP level of XL and the exact same OS, just on two different pc's? Because MS Query decides when it wants to return data as a date or as a string?

Here is what I'm getting on a worksheet from a user's pc that does render the desired results:

Format Cells > Number > Category: Date > Type: 3/14/01 Display value: 9/30/09 Formula Bar value: 09/30/2009 1:18:14 PM

Format Cells > Number > Category: General Display value: 40086.55433 Formula Bar value: 40086.5543287037

Here is what I'm getting on a worksheet from a user's pc that does not render the desired results:

Format Cells > Number > Category: Date > Type: 3/14/01 Display value: 2008-04-28-08.56.07.000000 Formula Bar: 2008-04-28-08.56.07.000000

Format Cells > Number > Category: General Display value: 2008-04-28-08.56.07.000000 Formula Bar: 2008-04-28-08.56.07.000000

 

[SkipVought]

Again, if you only want the date, then ONLY RETURN THE DATE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[brookwood]

Ok, thanks Skip.

Any who...if another user has seen this annomoly where MS Query returns a proper date format to the worksheet for some users and a string to others where the data type from the relational database is date, then please feel free to comment with your suggestions.

I just can't imagine that MS Query randomly chooses to return string in some instances and date in other.

Maybe I'm just posting in the wrong forum. Should I be posting at the IBM DB2 forum instead?

 

[SkipVought]

Select ..., Date(MyScrewedUpDateTime)...
from...

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, I have never seen this exact case, but I have seen instances where database data IS screwed up and you have to program around bad data. Its just a fact of life.

No, I seriously doubt that MS Query changes ANYTHING in your timestamp willy-nilly.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[brookwood]

Uncle.

Better to fix it from the data side than try to figure out what MS is up to.

Much gras Skip.

Brookwood