CR 8.5, date & Time Problem, Please help 1

[dreman]

I though this should be simple but it is not.
Using CR 8.5 ODBC with SQL 2000:
I define ?user_date as range date (Avoiding time on purpose)
I found out when running query that if you select today the time are 0.00 which is today midnight to today midnight and will ignore any records with today date after 6 pm or ...
How can I have a function to ignore the time and look at the date only. Please advise, Thank you.
dré

 

[synapsevampire]

If you're comparing a single date only parm to a datetime, try something like this:

(
{MyTable.MyDatetime} >= DateTime(year({?date parm}),month({?date parm}),day({?date parm}),0,0,0)
and
{MyTable.MyDatetime} <= DateTime(year({?date parm}),month({?date parm}),day({?date parm}),23,59,59)
}

I address this by creating two variables, start date and end date and doing the correction in those formulas and then referencing the formulas in the record selection criteria compare with the datetime. The reason being is that I have multiple parms and the SQL won't get passed otherwise.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dreman]

Sorry, I am still having a problem.
I am defining the following in a formula.
// trying to get from date as date iwith time stamped
// 0mins...
Local DateVar d1:= Minimum({?date parm});
Local DateVar d2;
d2 = DateTime(year(d1),month(d1),day(d1),0,0,0)

I get a true of false as a result, I need it as a date with Time. Am I missing something. Please advise, Thank you.
Dré

 

[synapsevampire]

Just missed the last part.

This may not correctly pass the SQL though, using variables will often times corrupt pass through.

// trying to get from date as date iwith time stamped
// 0mins...
Local DateVar d1:= Minimum({?date parm});
Local DateVar d2;
d2 = DateTime(year(d1),month(d1),day(d1),0,0,0);

d2

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dreman]

Yes you are right it will not pass to SQL which is very critical (We have over a million records and counting up).

Do you know what is the best way to have parameter dates compared with SQL dates that should be passed to SQL query. Please advice. Thank you.
dré

 

[synapsevampire]

Did you try what I'd posted???

I have many posts here regarding passing SQL, the trick is to NOT use variables, and wrap the functions in parens.

It looks like you're using a date range parm, so just convert those:

(
{MyTable.MyDatetime} >= DateTime(year(minimum({?date parm})),month(minimum({?date parm})),day(minimum({?date parm})),0,0,0)
and
{MyTable.MyDatetime} <= DateTime(year(maximum({?date parm})),month(maximum({?date parm})),day(maximum({?date parm})),23,59,59)
)

You may also be able to create a single line to do this as in:

{MyTable.MyDatetime} >= DateTime(cdate(minimum({?date parm})), cdate(&quot;0,0,0&quot
and
{MyTable.MyDatetime} <= DateTime(cdate(maximum({?date parm})), cdate(&quot;23,59,59&quot

<not tested, but I think that's right>

If the above doesn't pass, let me know.

You'll probably have to create 2 formulas, one with a start datetime, and one with a end datetime to accomplish the above, and then reference those formulas in the record selection criteria.

And remember, variables confuse the pass through sql generator, avoid them for anything referenced by the record selection criteria.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dreman]

K:
Thanks for the quick response, I will try this tomorrow and will definetely let you know.
Thanks dré

 

[dreman]

K:
No the formula does not work
I cretaed two formula to have time stamped in them, however I am not able to pass them to the SQL query ????
Please help.
dré

 

[Ngolem]

SV - he really doesn't want to compare the time and you won't get a match if the time on the otherside is anything but 0 hours/mins/secs

I don't have CR8.X so I cannot test....but is it possible using a SQL expression to convert the dateTime to a date...then the comparison would be easy and passed down? Jim Broadbent

 

[dreman]

Resolved it, Resolved it, Resolved it, Resolved it, Resolved it, Resolved it, Resolved it,
This is how. It is soooo simple.
In crystal report, go to file, Report Options, Convert Date-time field must be set to 'To Date-Time'.

(Another complex solution is to convert date-time in SQL to date, but formula is too complex and in SQL there is no function to get date only)

Thank you for you help.
dré

 

[synapsevampire]

ngolem: You're mistaken, look again.

http://www.informeddatadecisions.com

kai@informeddatadecisions.com