[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 741
  • 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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