• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 744
  • Last Modified:

Help for Record Selection Formula

Hi people,
Can you help me to translate in Crystal Report syntax this SQL condition?

TRUNC(datdep) <= NVL(TRUNC(TO_DATE('" & Request("src_datarr") & "','dd/mm/yyyy')),TRUNC(TO_DATE('31/12/2999','dd/mm/yyyy')))
AND TRUNC(datarr) >= NVL(TRUNC(TO_DATE('" & Request("src_datdep") & "','dd/mm/yyyy')),TRUNC(TO_DATE('01/01/1900','dd/mm/yyyy')))

where
- "datdep" and "datarr" are my database fields (date format)
- Request("src_datdep") and Request("scr_datarr") are my research parameters (string format)

I need it to extract the records within the specified data range.
Thanks in advance
0
aletheia1
Asked:
aletheia1
  • 10
  • 6
1 Solution
 
GJParkerCommented:
did you try the Date() function  I suggested in your previous post ?

Gary
0
 
mlmccCommented:
Can you show the data for datdep and datarr?

If it is as your previous post implies 1st January 2005 then it may take some manipulation to make it into a date format that is useable.

mlmcc
0
 
aletheia1Author Commented:
- The date format in the database is: dd/mm/yyyy 24hh:mm
- with the TRUNC function I get: dd/mm/yyyy
because I need to compare it with Request("src_datarr") and Request("src_datdep")
- Gary, I can use DATE function, but what about "NVL"? (if Request objects are empty, it returns default values. In my example '31/12/2999' and '01/01/1900')
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
aletheia1Author Commented:
I've tried
Date({Command.DATPAR}) <= Date(CDate({?field_datfin})) AND
Date({Command.DATARR}) >= Date(CDate({?field_datini})) AND...

But I got the error: "A date or time is required here" (inside comma of Date function)

And I still need something for NVL...
0
 
aletheia1Author Commented:
To avoid the problem of the empty parameters, maybe I can manage it from ASP... what do you think?

Set Param1 =  Session("ParamCollection").Item(1)
If Request("src_datdep") <> "" Then
      Call Param1.SetCurrentValue(CStr(Request("src_datdep")))
Else
      Call Param1.SetCurrentValue(CStr("01/01/1900"))      
End If


So now I just need help about Date / CDate functions... please!
0
 
GJParkerCommented:
Your last post is how I would deal with this as CR doesn't have an equivalent function.

The DateTime problem is caused by the Fields you are trying to convert is not a DateTime.

What is the data type of DATPAR and DATARR ?

Try this..

CDate({Command.DATPAR}) <= CDate({?field_datfin}) AND
CDate({Command.DATARR}) >= CDate({?field_datini})

Gary
0
 
aletheia1Author Commented:
- DATPAR and DATARR are Date format (dd/mm/yyyy 24hh:mm)
- ?FIELD_DATFIN and ?FIELD_DATINI are String format. (dd/mm/yyyy)

If I try your suggestion, I got the error
"Error Occurred Reading Records: Bad date format string."

I think I need to convert DATPAR and DATARR in this format --> dd/mm/yyyy (without hours and minutes), but I don't know how to do from Crystal Report.
0
 
GJParkerCommented:
The CDate function converts the datetime to just date format.

The error message infers the SQL is processing but failing on converting a record form the db, I think the problem could be caused by your data you may have a record which contains a value which isn't a correct date.

Can you run the report through the CR Designer and test the data.

You may need to deal with this in the ASP

Set Param1 =  Session("ParamCollection").Item(1)
If IsDate(Request("src_datdep")) Then
     Call Param1.SetCurrentValue(CStr(Request("src_datdep")))
Else
     Call Param1.SetCurrentValue(CStr("01/01/1900"))    
End If


Gary
0
 
aletheia1Author Commented:
I can guarantee that all date in DB are correct...

If I run the report through the CR Designer I just get the error "Bad date format string" --> CDate({?field_datfin})

But... wait a moment... I created {?field_datfin} and {?field_datini} as string type... should they be date?
0
 
aletheia1Author Commented:
If I change them to DATE type, the error become "A date-time is required here" --> {?field_datfin}
0
 
GJParkerCommented:
Your parameters could be date but remember to change the ASP conversion type when setting the parameter value.

Selection formula would also be

CDate({Command.DATPAR}) <= {?field_datfin} AND
CDate({Command.DATARR}) >= {?field_datini}

Gary

0
 
aletheia1Author Commented:
Oh, sorry! If I change them to DATETIME type, the report through the CR Designer works!!!

But in the ASP application, I get the error "Type mysmatch"... I suppose I should convert somehow there, too...
0
 
aletheia1Author Commented:
Even if I use CDate in ASP, I get "Type mysmatch"

If Request("src_datpar") <> "" Then
      Call Param1.SetCurrentValue(CDate(Request("src_datpar")))
Else
      Call Param1.SetCurrentValue(CDate("01/01/1900"))      
End If
0
 
GJParkerCommented:
Try

If Request("src_datpar") <> "" Then
     Call Param1.SetCurrentValue(CDate(Request("src_datpar")))
Else
     Call Param1.SetCurrentValue(CDate("01/01/1900 00:00:00"))    
End If

Gary
0
 
aletheia1Author Commented:
I don't know why but CDate give me some problem.

If I print out Param2.CurrentValue, it returns
12/31/2999 11:59:59
instead of
31/12/2999 23:59:59

(I need DD/MM/YYYY 24HH:MM format)
0
 
GJParkerCommented:
Check your Datetime settings in CR and on the server.

Gary
0
 
aletheia1Author Commented:
You're right, the server hade wrong date format.

I think I need to restart it, because I still get the old format from ASP, but I can't do now.
I will be back to inform you what happened.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 10
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now