Date format from SQL 2008

building semantic layer
1

Hi All,

I am using BOXI 3.1 and sql server 2008. Date fields in the server are
as ‘DateTime’ and then converted to Date in BO designer.

I was expecting to see, the object results in webi as :

dd/mm/yyyy

but they are showing as

mm/dd/yyyy

It’s only this universe that has this date format issue…

Anyone any ideas?

Thanks

Bell_33 (BOB member since 2010-07-05)

2

Not got access to SQL Server 2008 to test it but SET DATEFORMAT dmy in your connect init parameter in the universe connection may help.

3

The prompy has accepted the dd/mm/yy format however, the data in the reports still mm/dd/yy

Bell_33 (BOB member since 2010-07-05)

4

So it brings the data back correctly for 25th November (not looking for the 11th day of the 25th month) but doesn’t display correctly?

If so, check the date format on the objects.

5

Sorry I got the wrong end of teh stick there…

I have gone to designer and in parameters/connect init

entered ‘SET DATEFORMAT dmy’

Is that what I was supposed to do?

Bell_33 (BOB member since 2010-07-05)

6

Yeah, it’s more of an educated guess than anything else.

We had to do that when working with an American database in SQL.
Never tried it in BO, but I’m hoping this will do the same thing for you.

7

This has not worked…please advise any other ideas?

Bell_33 (BOB member since 2010-07-05)

8

Is it pulling the right data back?

9

Right data wrong format…

There is another datetime field in this table at sql level and when this changed to date at designer level all is fine when viewing this object at deski level…wierd issue…

Bell_33 (BOB member since 2010-07-05)

10

Please note that when you change the format it only affects new report objects, not existing ones.

11

Tired this morning all fine…dont know what happened and why…might be one those restart things…very strange…

Bell_33 (BOB member since 2010-07-05)