Date time conversions

[addygoyal]

hi there
i ve been jugling with this particularly messy one hope you ppl can find something

I ve got two fields called {Date) and {Time}which stores date and time values repectively but as strings
so the example data is like
So the different records are:
{Date) {Time}
2003-8-19 0040
2003-8-19 1230
2003-8-20 1650
2003-8-21 2300

what i want to do is select all the records lying between two dates and two times which i am taking as parameters. The parameter fields with example values are as follows

{?FromDate) 2003-8-19 {?FromTime} 0000 and
{?ToDate) 2003-8-21 {?ToTime} 2400

Presently i am taking these parameters as strings but i can change them to any other data type if it is required in the solution
but Data base fields are always going to remain strings and in the above given format

Regards and Thanx
Addy

 

[Madawc]

I had a similar problem, my stored procedure produced dates as a YYYY-MM-DD string. I turned the value into a date using
Cdate (left({Query.MStmp}, 10))

You've got an extra complication, month without a leading zero for single-digit months. I've no idea if Crystal is clever enough to copy, try and see. Put a commmand in a formula field and then display the result to see if it is correctly handled.

Once you have real dates, you can combine them with time and make sensible selections.

Madawc Williams
East Anglia, Great Britain

 

[synapsevampire]

Depending upon your database and Crystal version, you can use a SQL Expression to construct a real datetime data type, and then use real datetime parameters to extract the data accordingly.

This will not work in you're using an SP, but if you're using an SP, use datetimes in the SP as parameters, and convert the strings fields to real datetimes as output in the SP.

Consider posting meaningful information about your environment (version of CR and database).

Don't worry over the single digit issue, that's easy to work around by parsing the date.

But I would be concerned over designs that store dates and times as different string fields in a database - bad architecture.

-k

 

[addygoyal]

ya synapse

the sql expression does the trick
By the way i am using CR8.5 and DB2 as database

Regards and Thanks to all
Addy