"FormatDate" Function

[TJW]

Hello,

We are currently having an issue with the "FormatDate" Function.

Results from the plan file locally and the uProduce server are
different.

In our data we have “2012-05-01”

This needs to be converted to “1 May 2012”

If we use
FormatDate( SubString(|->[Service_Cease_Date], 8,2) +"/"+
SubString(|->[Service_Cease_Date], 5,2) +"/" + SubString(|-
>[Service_Cease_Date],0, 4) , "d MMMM yyyy")
Locally we get “1 May 2012” but by using the same code on the uproduce
server we get “5 January 2012”

If we swap the month and date parameters around e.g.
If we use
FormatDate( SubString(|->[Service_Cease_Date], 5,2) +"/" +
SubString(|->[Service_Cease_Date], 8,2) +"/"+ SubString(|-
>[Service_Cease_Date],0, 4) , "d MMMM yyyy")
On uProduce we get “1 May 2012” but by using the same code locally
using a plan file we get “5 January 2012”

Does anyone have any idea what is going wrong?

Thanks,
TJW

[George Marsh]

Terry have you checked your uProduce server's date setting matches
your dev machines?

[TJW]

I've changed the "Regional and Language Options" to UK instead of US
but I'm still getting the same error. Do you think I would need to do
a restart on the servers? Thanks Terry.

[Timothy Perrett]

What type of data source are you using? I bet its excel.

[Bill]

I guess your input string looks like "yyyy?MM?dd" (not "yyyy?dd?MM").
Could you confirm?

As written in the uPlan User's Manual, the first parameter provided to
FormatDate must be a ... date.
I would first cast the input string into a Date, then call the
FormatDate function.
FormatDate(AsDate(SubString(|->[Service_Cease_Date], 8,2) +"/"+

SubString(|->[Service_Cease_Date], 5,2) +"/" + SubString(|-

>Service_Cease_Date],0, 4)), "d MMMM yyyy")

I also would use temporary, intermediate variable for debugging
purpose.

[TJW]

Hi Tim,

We are using Microsoft access as a data source. The data type in the
table is set to text.

[TJW]

Hi Bill,

Yes it is, the data is YYYY MM DD. In any case we shouldn't be getting
different outputs depending on where the function is used. I've gone
into "Regional and Language Options" and change this on the server.

Previously when I was going into CMD and typing date the date was
showing as MM DD YYYY. After I changed the settings in "Regional and
Language Options" the date in CMD started showing as DD MM YYYY. But
still not having any luck.

Thanks
Terry.

[Guillaume Besnier]

I know it's ugly, but until you find the actual reason of the issue,
would you consider add a slight condition in your QLingo? Such as

if (GetEnv("HostApplication") == "uProduce")
{
FormatDate(...)
}
else
{
FormatDate(...)
}

-------- Message original --------
Sujet: [xmpie-users] Re: "FormatDate" Function
De : TJW <terry....@incdirect.co.uk>
Pour : XMPie Interest Group <xmpie...@googlegroups.com>
Date : 23/04/2012 15:00

[Timothy Perrett]

Access / Excel... both use the JET engine under the hood and have issues inferring textual dates. Humor me and make a dummy access database that has some date fields that properly specify timestamp or date time in a sandbox campaign to see if it does the same within QLingo? When dealing with dates you really need to rely on the database engines temporal systems as much as possible, and i'd guess thats whats happening here.

[Igor Vorobeychik]

just to rule out any QLingo bug , can you create JS or VB function and
do the same , both languages has lots of date functions.

[Timothy Perrett]

Personally i'd avoid JS for date manipulation: its unintuitively zero-indexed for date operations and has a horrible, horrible date API. Can't comment on VB in this instance :-) 

[George Marsh]

I've experienced static date variables in uPlan changing from being
read as #MM-DD-YY# to #DD-MM-YY# before, assumed it was due to an
environment change.

Will future uPlan versions support the new MS SQL Express LocalDB mode?

If so we could probably retire Access from our workflow ...

[TJW]

Hi Bill,

FormatDate( AsDate(SubString(|->[Service_Cease_Date], 8,2) +"/"+

SubString(|->[Service_Cease_Date], 5,2) +"/" + SubString(|-

>[Service_Cease_Date],0, 4)) , "d MMMM yyyy" )

This has worked. Thanks for your help.