Date format string GMT/BST

building reports webintelligence
1

BO XI 3.1 Sp6 ( we will be upgrading to BI 4.2 within 8 months).

I need to know if possible to return a date format of either “GMT” or “BST” from a date that exists in a table. We have no control over the table so cannot create a new column using Oracle functions, and we are unable or allowed to write a data extract from the database into our own operational data store, I am afraid I cannot go into the stupid politics of that. There are over 1500 date time fields in many tables. The owner of these tables decided that only a handful of date/time fields will be changed to cater for clock changes in the UK dealth with at the front end of there application before it’s inserted into the table, but not all, and they are now busy identifying them all. We have told them that they need to tell us which one so we can amend the time accordingly for our customers that will be writing reports. Yes the company in question whom built the application and the database structure exceptionally badly managed and thought about.

We have two options one is to decode every date/time object in the many universe we have built, which is a risk, resource intensive and support nightmare. The other option is to put the oneous on the customer, we will amend the dimension name accordingly to let them know they need to offset the date/time from the results they get back in the Web Intelligence report,but in order to do this I need to be able to use the function FORMATDATE([myDate];“GMT/PST”) something like that so I only get back GMT or BST, is it possible, is there another function, can JavaScript return just one set of those three characters?

Update : FormatDate([myDate];“z”) only ever gives me the results GMT +00:00 regardless of date. Close but not close enough clock change in UK was Sunday 27th March 2016, all dates prior to are GMT, on and after this date they are BST, but in the function example it only ever shows GMT. So grrrrr , still need help please.

Macroman :uk: (BOB member since 2002-11-13)

2

As there is no dedicated Format string to return GMT or BST I have figured it out another way by calculating the last Sunday in March and also in October as follows;

varLastSundayMarch
=ToDate(LastDayOfWeek(RelativeDate(LastDayOfMonth(ToDate(“01/03/”+FormatNumber(Year([MYDATE]);“0”);“dd/MM/yyyy”));-6)) +" 01:00:00";“dd/MM/yyyy HH:mm:ss”)

varLastSundayOctober
=ToDate(LastDayOfWeek(RelativeDate(LastDayOfMonth(ToDate(“01/10/”+FormatNumber(Year([MYDATE]);“0”);“dd/MM/yyyy”));-6)) +" 02:00:00";“dd/MM/yyyy HH:mm:ss”)

varGMTorBST
=If([MYDATE]>[varLastSundayMarch] And [MYDATE] < [varLastSundayOctober];“BST”;“GMT”)

Replace [MYDATE] with the date dimension object you want to run this on, alas will have to replicate the variables for each date dimension object you have.

With the function/formulas now in place we can control the returned results either on what’s displayed or by offsetting an hour in calculations.

Macroman :uk: (BOB member since 2002-11-13)