gummio
Posts: 11
|
| Posted: 04/28/2004, 3:49 AM |
|
Hi,
I have this sql and when i run it in sql query analyser itīs works ok, but when i put in in CCS and set the parameter for the dates itīs not working.
This is the SQL in query analyser and if i copy paste this in to the data source window for the grid, and when i run it i get the result. because the dates are already there but when i try to use the parameter i get a syntex error, date converse error..
SELECT b.ID_Bill, b.Bilnumer, view_Bilheiti.Bilheiti, view_KmPerBil.KmStada as HeildarKm,
((SELECT MAX(KmStada)
FROM Bilar_Kilometrastada
WHERE Bill_ID = b.ID_Bill and (DagsStada BETWEEN DATEADD(dd, - DAY(CONVERT(DateTime, '01/02/2004 00:00:00', 103)) + 1, CONVERT(DateTime, '02.01.2001 00:00:00', 103))
AND DATEADD(dd, - 1, DATEADD(mm, 1, DATEADD(dd, - DAY(CONVERT(DateTime, '02.01.2004 23:59:59', 103)) + 1, CONVERT(DateTime,
'02.01.2004 23:59:59', 103)))))) -
(SELECT MAX(KmStada)
FROM Bilar_Kilometrastada
WHERE Bill_ID = b.ID_Bill and (DagsStada BETWEEN DATEADD(dd, - DAY(CONVERT(DateTime, '02.01.2001 00:00:00', 103)) + 1, CONVERT(DateTime, '02.01.2001 00:00:0', 103))
AND DATEADD(dd, - 1, DATEADD(mm, - 1, DATEADD(dd, - DAY(CONVERT(DateTime, '27.01.2004 23:59:59', 103)) + 1, CONVERT(DateTime,
'02.01.2004 23:59:59', 103))))))) AS LastKm
FROM Bilar_Listi b LEFT OUTER JOIN
view_KmPerBil ON b.ID_Bill = view_KmPerBil.Bill_ID LEFT OUTER JOIN
view_Bilheiti ON b.Biltegund_ID = view_Bilheiti.ID_Bilgerdir
WHERE (b.Afskradur = 0)
ORDER BY b.Bilnumer
This is how i use it in CCS:
.....
((SELECT MAX(KmStada)
FROM Bilar_Kilometrastada
WHERE Bill_ID = b.ID_Bill and (DagsStada BETWEEN DATEADD(dd, - DAY(CONVERT(DateTime, {ParDate}+' 00:00:00', 103)) + 1, CONVERT(DateTime, {ParDate}+' 00:00:00', 103))
AND DATEADD(dd, - 1, DATEADD(mm, 1, DATEADD(dd, - DAY(CONVERT(DateTime, {ParDate}+' 23:59:59', 103)) + 1, CONVERT(DateTime,
{ParDate}+' 23:59:59', 103))))))
.......
The parameter comes from a text box with a datepicker and is passed by url. The date format i am using is DD / MM / YYYY
Can anybody help?? please
Regards
Gummio
|
 |
 |
peterr
Posts: 5971
|
| Posted: 04/28/2004, 12:21 PM |
|
Hello,
Couple suggestions:
1. I'm not sure if you defined the "ParDate" parameter in addition to the SQL (below the SQL editing area in CCS). Only when parameters are defined then the program knows how to convert for example a URL parameter into a value for use within SQL and will convert {ParDate} to the appropriate value. The "Date" type can be specified there as well. If you already defined such parameter please specify how you configured it, as it may provide some clues.
2. In the Before Execute Select (or Before Select) event of the form, add the debugging code as shown at http://docs.codecharge.com/studio/html/ProgrammingTechn...yingOutput.html
The goal would be to view the final SQL as it will be executed within your form. Once you see the SQL, you can analyze if the parameter values were converted properly.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
|
