Dates in Text Objects

[#1]

Hi,

I've been reading around and haven't been able to find any information on this, so i'll give this a shot.

I would like to create a label via the Text object that displays something along the lines of

'Information from (start date) to (end date) for so and so'

I've put this:

date#(@10,'MM/DD/YYYY') as [Date of Service], Month(@10) as Month, Year(@10) as Year,
date(DayStart(@10), 'MM/DD/YYYY') as SDay,
date(DayEnd(@10), 'MM/DD/YYYY') as EDay,

in my script, but when I enter this

='Data dates from ' & [SDay] & 'to ' [EDay]

into the expression box, I get a null value.

Any idea where I'm going wrong?

[pover]

You're missing a & between to and EDay.

='Data dates from ' & [SDay] & 'to ' & [EDay]

You might also need to put min(SDay) and max(EDay).

Regards.

[#3]

='Data dates from ' & min( [SDay]) & 'to ' &max( [EDay])

yields

"Data dates from to "

It doesn't bother displaying the dates.

[pover]

If you make a list of SDay, what values do you get?

Also, are the values aligned to the left or the right? If they are aligned left they are text and if they are aligned right they are numbers. You need them to be numbers.

Regards.

[#5]

Well it appears that there are no values in the text box either. Date of Service is importing in as text, but it is showing.

I guess there's a conversion method to switch it from text to numeric then convert it back into a date?

[pover]

In you script, try the following:

date(date#(@10,'MM/DD/YYYY')) as [Date of Service],
Month(date(date#(@10,'MM/DD/YYYY'))) as Month,
Year(date(date#(@10,'MM/DD/YYYY'))) as Year,
DayStart(date(date#(@10,'MM/DD/YYYY'))) as SDay,
DayEnd(date(date#(@10,'MM/DD/YYYY'))) as EDay,

A load of a load might be even better so that you don't have to repeat date(date#(@10,'MM/DD/YYYY')) so many times.

Regards.

[#7]

Still nothing. 😕 The list box doesn't show anything for SDay.

I'm wondering at this point if it's the format the dates are in when imported. They come in as:

05-OCT-09

DD-MM-YY

[pover · Accepted solution]

Try this then

date(date#(@10,'DD-MMM-YY')) as [Date of Service],
Month(date(date#(@10,'DD-MMM-YY'))) as Month,
Year(date(date#(@10,'DD-MMM-YY'))) as Year,
DayStart(date(date#(@10,'DD-MMM-YY'))) as SDay,
DayEnd(date(date#(@10,'DD-MMM-YY'))) as EDay,

[#9]

Ah progress.

That shows the date, but it's got an ugly time stamp next to it now.

"Data dates from 12/7/2009 12:00:00 AM to 2/10/2010 11:59:59 PM"

So I changed my expression to:

='Data dates from ' & date(min( [SDay]), 'MM/DD/YYYY') & 'to ' & date(max( [EDay]), 'MM/DD/YYYY')

and I have

"Data dates from 12/07/2009 to 02/10/2010"

Just to verify, in the script it's specifying the format it's in and not as I'd like it?

[pover]

In the function date#() you specify the format it is in to convert the text to date and then in the function date() you you can specify the format to convert it to or if you don't specify a format it will convert to the format defined in the system variables.

Regards.