passing date parameters to sql script 1

[Macho123]

Hi I am using crystal reports 11 and I have a problem when I am trying to pass the date parametrs in to the sql script at the add command facility. I am getting the following error.

Failet to retrive data from the Database,
Missing right parantesis.

but when I run the same query in oracle it is working fine. could anybody help me out with this.

Thanks,
krant

 

[lbass]

Please copy and paste the query into the thread.

-LB

 

[Macho123]

This is the Query I am using and P1 and p2 are parameters of date type.

select id , last_name, start_date, city from employee

where start_date between to_date('{?p1}','yyyy/mm/dd')

and

to_date('{?p2}','yyyy/mm/dd')

Thanks...

 

[lbass]

Try:

select id, last_name, start_date, city
from employee
where start_date >={?p1} and
start_date < {?p2}+1

-LB

 

[Macho123]

Got it Thanks very much.

I do not understand why in crystal reports it do not accent between to_date in the 'command'.

One more question is while entering the parameters there I am getting two blank fields where i coulod enter the values for both start and end dates. so in total in the 'enter parameters window' there are four fields and I am getting a calender from where the dates to be selected select. Is there a way that I could get only 2 fields to be filled and in that the default value should be 'yyy/mm/dd' so that the user can enter any specific date he wants.

Any ways thanks for the help.

Krant

 

[lbass]

I think you could alternatively use:

select id, last_name, start_date, city
from employee
where start_date between to_date(to_char({?p1},'YYYY/MM/DD'),'YYYY/MM/DD') and
to_date(to_char({?p2},'YYYY/MM/DD'),'YYYY/MM/DD')

I can't think of why you would be getting two sets of prompts unless you are also using a subreport with the same prompts and you did not link them.

-LB

 

[Macho123]

Thanks Lbass.

I am not using any sub report it is only one report and out of the two fields what i am getting in the parameter window one is drop down which has present date as default and the other field not a drop down and it has a small calendar signe to its side and I could not enter any value in the first field but select only the one present date from the drop down in the first field and if I have to enter a value i could do it only in the second field, so stricly speaking i do not need the first field at all so that i could have only the second filed so that user could enter the data in to it. please look in to it and let me know.
I have one more question is there a way to give the value mm/dd/yyyy insted of present yyyy/mm/dd.

Thanks...

 

[lbass]

Did you create the parameters both IN the command and in the GUI? I think I would start a new report, copy the current command, and create the parms within the command area only. Then you should only get one set.

Regarding the date format, if you are asking whether you can change the way they must be entered in the parameter screen, I don't know of a way to do this.

-LB

 

[Macho123]

Thanks vay much Lbass it is working now with copying the sode in to a new report. Thanks vey much yes I was asking the entry of date format for the parametes, but every thing helped me a lot.

Regards,
krant.

 

[Macho123]

Lbass I have one more doubt how do I make the default values for my parameters as previous months starting date and previous months ending date if the report is running on the present months first day. Please help me on this.

Thanks...

 

[lbass]

I think you have to set up a default value like:

2999-9-9

...and create prompt text like:

"To use day 1 of the previous month, select 2999-9-9; otherwise, select a start date." Add similar text for your prompt for the enddate. Then set up your record selection formula like this:

(
if {?start} <> date(2999,9,9) then
{table.date} >= {?start} else
if {?start) = date(2999,9,9) then
{table.date} >= dateserial(year(currentdate),month(currentdate)-1,1)
) and
(
if {?end} <> date(2999,9,9) then
{table.date} < {?end} + 1 else
if {?end) = date(2999,9,9) then
{table.date} < dateserial(year(currentdate),month(currentdate),1)
)

-LB

 

[lbass]

On second thought, you might have to set this up like this in a command:

(
(
{?start} <> date(2999,9,9) and
{table.date} >= {?start}
) or
(
{?start) = date(2999,9,9)and
{table.date} >= dateserial(year({fn NOW()}),month({fn NOW()})-1,1)
)
) and
(
(
{?end} <> date(2999,9,9) and
{table.date} < {?end} + 1
) or
(
{?end) = date(2999,9,9) and
{table.date} < dateserial(year({fn NOW()}),month({fn NOW()}),1)
)
)

I'm not sure whether {fn NOW()} is the best choice for currentdate--you could probably tell ME what the correct function should be.

-LB

 

[Macho123]

i did the same thing but the default value even after setting it, the parameters are not getting the default values when i run the report.

 

[Macho123]

I got the default values but when I run the report I am not getting the data in the report. Below the formula I used and data in the tables.

This is the formula I have used for record selection and I have set the default parameters for p1 and p2 as 9/9/2999.

if {?p1} <> date(2999,9,9) then
{Command.START_DATE} >= {?p1} else
if {?p1} = date(2999,9,9) then
{Command.START_DATE} >= dateserial(year(currentdate),month(currentdate)-1,1)

and
(
if {?p2} <> date(2999,9,9) then
{Command.START_DATE} < {?p2} + 1 else
if {?p2} = date(2999,9,9) then
{Command.START_DATE} < dateserial(year(currentdate),month(currentdate),1)
)

the data in the table is as follows, I am mentioning the start_date column in the table with its name below start_date is one column and sal is other column.
Start_date/sal
10 ram ram 9/1/2007 10/1/2007 123.0

10 ram ram 9/2/2007 10/1/2007 123.0

10 ram ram 9/8/2007 10/1/2007 123.0

01 Jason Martin 7/25/1996 7/25/2006 1234.5

02 Alison Mathews 3/21/1976 2/21/1986 6661.7

For the above data I have set the system date to oct 1st 2007 so that if the report runs atleast those 3 0r 4 columns which are in sept 2007 should show up but it is not showing.

Lbass please help me.

Thanks...

 

[lbass]

First, you omitted most of the parens I showed in my post, and they are not optional. Also, the second part of your statement is still using the start date, when it should be using the end date.

In addition, you might want to also allow for records where there is no end date--not sure.

-LB

 

[Macho123]

The filter criteria is only on start date not on end date. I want the report to run on the range of start date values.

Example start between sep 1st and sep 30 th. End date is not at all a consern and it is not included in the report. Those details above were directly from the table. This is the code now.

(
if {?p1} <> date(2999,9,9) then
{Command.START_DATE} >= {?p1} else
if {?p1} = date(2999,9,9) then
{Command.START_DATE} >= dateserial(year(currentdate),month(currentdate)-1,1)
)
and
(
if {?p2} <> date(2999,9,9) then
{Command.START_DATE} < {?p2} + 1 else
if {?p2} = date(2999,9,9) then
{Command.START_DATE} < dateserial(year(currentdate),month(currentdate),1)
)

Thanks...

 

[lbass]

Sorry, I wasn't paying attention. Your last code should work.

-LB

 

[Macho123]

but it is not working i do not know why. when I try to run the report the default values are showing up in the parameters field and the code is also correct and the date given in the code and the default date are matching except that in the default parameters the date i have given is in this format. 9/9/2009 this is at the command and in the formula it is 2009/9/9 i am not sure if this is the problem I also tried to change the default parameters to 2009/9/9 but even then it is not working. Please help me out in this

Thanks...

 

[lbass]

Please paste the entire command into the thread. Please also explain what isn't working in terms of the data you are getting back.

-LB

 

[Macho123]

below is my command. the default parameters for p1 and p2 are 9/9/2999, 9/9/2999

select id, last_name, start_date, city
from employee
where start_date between to_date(to_char({?p1},'YYYY/MM/DD'),'YYYY/MM/DD') and
to_date(to_char({?p2},'YYYY/MM/DD'),'YYYY/MM/DD')

here is the formula i am using for selecting the records.

(
if {?p1} <> date(2999,9,9) then
{Command.START_DATE} >= {?p1} else
if {?p1} = date(2999,9,9) then
{Command.START_DATE} >= dateserial(year(currentdate),month(currentdate)-1,1)
)
and
(
if {?p2} <> date(2999,9,9) then
{Command.START_DATE} < {?p2} + 1 else
if {?p2} = date(2999,9,9) then
{Command.START_DATE} < dateserial(year(currentdate),month(currentdate),1)
)

the problem is according to the data in the table below i have changed the system date to oct 1st and ran the report so that i should get those 3 columns in sep month but it is not giving me those values. when i manually enter the parameters for those start and end dates of last month i am able to get the values but by default even after setting the parametes to take the last months dates it is not returning the values.
start_date
10 ram ram 9/1/2007 10/1/2007 123.0

10 ram ram 9/2/2007 10/1/2007 123.0

10 ram ram 9/8/2007 10/1/2007 123.0

01 Jason Martin 7/25/1996 7/25/2006 1234.5

02 Alison Mathews 3/21/1976 2/21/1986 6661.7

Thanks...