Date Manipulation/Calculations

[RobD]

I'm having an issues with Date manipulation. Basically I need to extract the current day month and year, which I've accomplished using the "datepart" function, then compare this to events in a database table stored in a MySQL Date field as 'dd/mm/yyyy'.

It all works fine except for any day before the 10th. The reason, seemingly is because "datepart" returns 9 for the 9th, but MySQL stores this date as 09. The missing zero plays havoc as I need to compare dates, but also add and subtract days from that date.

For example. I'm displaying a weekly calendar, which bases itself off the current date. To retrieve events for that week from the database I subtract the required number of days from the current date (i.e. on Friday I subtract 2 days to show Wednesdays events) and format a new date string from that to compare against the database date. This works fine until I compare sub-10th dates.

Any ideas how I can get around this? Also, I'm writing this in FPL at the moment, I can switch to Javascript if there is no way around it in FPL, but would prefer not as I've written a number of scripts already to do this and don't want to have to re-write them if possible.

[Jon]

How are you using datepart? I tried it like this:

DAY = datepart(D1, 'DM');

where DAY is a field of type CHAR and D1 is a field of type DATE and it returns "09". There is also the formatdate() function which you might try. And with Javascript you have much more flexibility with DateServices.formatDate().

Regards
Jon

[RobD]

As it turns out it was a bit of a PEBCAK issue

I was extracting the date parts and comparing them as string with the date fields from the database (which I converted to strings), which was causing an issue but if I compare them as Date types then it works fine.

Thanks for your help Jon