Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Date/Time Parameter help request 2

Status
Not open for further replies.

JetRamsey

Technical User
Oct 22, 2010
49
US
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
 
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)))
 
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 .
 
Converting from date to string should be easy (totext or cstr), then append the '00:00:00:00' to the string.
 
In the record selection formula, try the following and see if it works:
[tt][pre]date({Field})>= {?StartDate} and
date({Field})<= {?EndDate}[/pre][/tt]
 
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
 
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]
 
You can format numbers to be displayed almost any way you want. Either in a formula or formatting the object.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top