Crystal

[hlardieri]

Hi

I am having a problem with dates here.

The field in the table is a string(date/time) ex
20040106 12:01:01.57

I am trying to create a report where the user is promted to enter a item number and the start & ending dates(without time)

I have tried a couple of different convert formats nothing is working.

How can I get the report pick the data from the table with just the date and ignore the time?

Thanks,
Hope

 

[vidru]

If the "Date" string is always formatted like that(YYYYMMDD... etc.), you could use this as your record selection formula:

Date(tonumber(left({Table.DateString},4)),tonumber(mid({Table.DateString},5,2)),tonumber(mid({Table.DateString},7,2))) in {?DateRange}

This won't pass through to the database (in the WHERE clause), so a SQL Expression would be a better solution if you're dealing with lots of data.

If you need more help, you might want to include some more information, like Crystal version, DB type, connectivity, etc.

-dave

 

[hlardieri]

The date string is alway the same.

The above did not work.

I have Crystal 8.5/ Sql 7.0 database

I need the user to enter a beg date and a ending date
and the report to grab the data between those dates without time being a factor.

Thanks,
Hope

 

[vidru]

Do your have your parameters set up? I'm guessing that you don't, or you didn't alter the above formula to use your parameter(s).

-dave

 

[vidru]

If you're interested in a pass-through version using a SQL Expression, create a SQL Expression Field called DateFromString, using the following (substituting your table.field name, of course):

/* %DateFromString */
convert(smalldatetime, substring(Table."DateString",5,2) + '/' + substring(Table."DateString",7,2) + '/' + left(Table."DateString",4) , 101)

Create your date range parameter ({?DateRange}), with a Value Type of Date, and check the "Allow multiple values" option.

In your record selection, enter:

{%DateFromString} in {?DateRange}

-dave

 

[pgtek]

hi
try this

2 formula here

@StartDate

Date(Tonumber({?BeginDate}[7 to 10]),Tonumber({?BeginDate}[1 to 2]),Tonumber({?BeginDate}[4 to 5]))


@EndDate

Date(Tonumber({?EndDate}[7 to 10]),Tonumber({?EndDate}[1 to 2]),Tonumber({?EndDate}[4 to 5]))


now create parameter ?BeginDate and ?EndDate

no linking just define as date click ok and exit

link parameter to match formula name
like @StartDate = ?BeginDate

and select your date field and right click select expert

and is between @StartDate and @EndDate

hope this helps






pgtek

 

[hlardieri]

I tried the above and it is telling me there is an error in the formula, it is looking for a string or an array

date(tonumber({?Enter Ending Date}

 

[pgtek]

Hi
try changing the parameter to number instead of date

cheers



pgtek

 

[hlardieri]

Hi,

Sorry but that did not work it is still looking for a string or array

 

[pgtek]

hi
try this

2 formula here

@StartDate

Date(Tonumber({YourDateField}[7 to ]),
TonumberYourDateField}[1 to 2]),
Tonumber({YourDateField}[4 to 5]))


@EndDate

Date(Tonumber({YourDateField}[7 to 10]),
Tonumber({YourDateField}[1 to 2]),
Tonumber({YourDateField}[4 to 5]))


now create parameter ?BeginDate and ?EndDate

no linking just define as number click ok and exit

link parameter to match formula name
like @StartDate = ?BeginDate

and select your date field and right click select expert

and is between @StartDate and @EndDate

hope this helps



pgtek

 

[pgtek]

OPS

@StartDate

Date(Tonumber({YourDateField}[7 to 10]),
TonumberYourDateField}[1 to 2]),
Tonumber({YourDateField}[4 to 5]))



cheers



pgtek

 

[hlardieri]

This worked however I had to chage the parameter to string
not number.

Thanks and have a great day.

 

[pgtek]

hi
your welcome u could have used this also

1. Create two parameters with String data types:

· Name the first parameter 'Startdate'
· Name the second parameter 'Enddate'

Do NOT select a default table or default field.

2. In the Prompting Text box, type a prompting message that ensures the input is the following format:

YYYY/MM/DDD

3. Create a Record Selection formula that uses the DTSToDate(DatetimeString) function with the parameter. For example,

{Orders.Order Date} in DTSToDate ({?Startdate}) to DTSToDate ({?Enddate})

This formula converts the parameter string to a Date type value so it can be compared against the records in the database.

cheers



pgtek