INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Date/Time Parameter help request

Date/Time Parameter help request

(OP)
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

RE: Date/Time Parameter help request

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)))

RE: Date/Time Parameter help request

(OP)
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 .

RE: Date/Time Parameter help request

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

RE: Date/Time Parameter help request

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

RE: Date/Time Parameter help request

(OP)
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

RE: Date/Time Parameter help request

Try the following:

Create a formula say, Todate_StatusDate

date(split({Status_Date}," ")[1]);


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

RE: Date/Time Parameter help request

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

RE: Date/Time Parameter help request

(OP)
Thanks everyone!!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close