Date/Time Parameter help request 2

[JetRamsey]

I'm using CR14 and accessing a SQL Server DB. There's a field called Status_Date that turns out to be a string field instead. An example value of this string field looks like 2016/08/01 00:00:00::00, which is 8/1/16 and will always stay 00:00:00:00 since there's another string field that grabs the time . I would like to use a Start Date parameter and an End Date parameter and the in between record select. Is there a way to easily do this? TIA

 

[kray4660]

Well there is no reason that you cannot set up a date parameter. It will just ignore the time part. The other step is to convert the string to a date. A formula something like this (replace dt with your field).

stringvar dt :='2016/08/01 00:00:00::00';

date(val(left(dt,4)),val(mid(dt,6,2)),val(mid(dt,9,2)))

 

[JetRamsey]

I probably didn't ask my question correctly, but thanks for the answer. I want to use a parameter as a START DATE. In CR14, I would create a parameter field and use "Date" as the parameter type. Then, do the same for an END DATE parameter. Normally, the Record parameter would be Date is between ?StartDate and ?EndDate. However, the start date for 8/1/2016 must convert into a string instead. Thus, needs converted to 2016/08/01 00:00:00:00 .

 

[kray4660]

Converting from date to string should be easy (totext or cstr), then append the '00:00:00:00' to the string.

 

[BettyJ]

In the record selection formula, try the following and see if it works:
[tt][pre]date({Field})>= {?StartDate} and
date({Field})<= {?EndDate}[/pre][/tt]

 

[JetRamsey]

Betty,

When entering date({Items.Status_Date})>= {?Start Date}, I get an error message of "Bad date format string". I'm guessing that since the "Items.Status_Date" field is a string, that's not going to work out.

Kray,

Thanks, I tried, but ran into other problems.
1. year({?Start Date}) gives me back 2016.00 instead of 2016 only
2. month({?Start Date}) gives me back 9 for September, instead of what's needed of 09 for 01 to 09 dates.

In the record statement, I tried to convert {?Start Date}) to say something like:

X > {?Start Date})

but when attempting to create X, I never could get the output to look exactly like:

2016/08/01 00:00:00:00

 

[BettyJ]

Try the following:

Create a formula say, Todate_StatusDate
[tt]
date(split({Status_Date}," ")[1]);[/tt]

In the record selection formula,
[tt]{@Todate_StatusDate} >= {?StartDate} and
{@Todate_StatusDate} <= {?EndDate}[/tt]

 

[kray4660]

You can format numbers to be displayed almost any way you want. Either in a formula or formatting the object.

 

[JetRamsey]

Thanks everyone!!!