Date format nightmare

Wed, 07 Oct 2015 11:08:48 GMT

Hi,
I have the following table imported from Excel, HTML and qvd:
Week	Member	Gym
WeekCommencing	Visit Date	Visit Date	Member	WeekCommencing	Gym
5/10/2015	5/10/2015	6/10/2015	John	5/10/2015	A
5/10/2015	6/10/2015	7/10/2015	Danny	5/10/2015	B
5/10/2015	7/10/2015	7/10/2015	Amelia	5/10/2015	C
5/10/2015	8/10/2015	9/10/2015	Anne	5/10/2015	D
I need to get the frequent vist of the member in every week
I tried to concatenate all 3 table, but I just could not get the Visist Date match correctly
When I select WeekComencing 5/10/2015, members visits from different date appears in the week as well like below:
WeekCommencing	Visit Date	Member
5/10/2015	6/10/2015	John
5/10/2015	7/10/2015	Danny
5/10/2015	7/10/2015	Amelia
5/10/2015	9/10/2015	Anne
5/10/2015	24/5/2015	Susan
5/10/2015	26/8/2015	John
When I look at the visit Date only in the Member Table, it shows
24/5/2015
26/8/2015
6/10/2015
7/10/2015
7/10/2015
9/10/2015
Those wrong date were aligning left. I have tried to use all sort of date or date# method or num(visit_date), but still not able to resolve it.
any idea what else I could do to resolve this issue?
And it is better to use concatenate or link table for this case?
Thanks,
David

Re: Date format nightmare

martyn_birzys — Wed, 07 Oct 2015 17:12:07 GMT

At first glance it looks like US date format MM/DD/YYYY, if that's the case use date# to fix it.

Apply date(floor(date(date_field))) to make sure all date fields are identical format.

Rgds

MarcoWedel — Wed, 07 Oct 2015 20:43:43 GMT

please provide random samples of your sources and your script to test with.

regards

Marco