ekendricks
Posts: 34
|
| Posted: 05/12/2006, 10:23 AM |
|
I'm trying to use a Stored Procedure as the data source in a report.
Using: MS Sql 2005 - CCS 3.0.3.1 - ASP
Slq Svr Date format: smalldatetime 05/01/2006 12:00:00 AM
When I run the SP from MS Mgmt Console inputing the parameter, ie: '05/01/2006' , the SP works and returns the correct data.
Now I'm trying to build a report, and when using the Visual Query Builder (show data), I get various errors : string data, right truncation -or- Invalid time format - or - Error converting data type int to smalldatetime.......The error depends on Data Type, format, size, scale, etc that I have selected. I have used various combinations of: Text - Date - DateTime-smalldatetime- etc.
Below is the beginning of the SP: Can anyone please tell me what I'm doing wrong, or what I need to do in order to satisfy the Visual Query Builder?
ALTER PROCEDURE [dbo].[sp_DailyActivitty]
@PostID smallint,
@Date_From smalldatetime,
@date_to smalldatetime
AS
-- mem_Accident
SELECT
dbo.Personnel.BadgeNo AS 'BadgeNo',
COUNT(CASE WHEN dbo.Accident.AccidentDate BETWEEN @date_from AND @date_to THEN 1 ELSE NULL END) AS 'Accidents',
SUM(CASE WHEN dbo.Accident.AccidentDate BETWEEN @date_from AND @date_to THEN ISNULL(dbo.Accident.Injuries,0) ELSE 0 END) AS 'Injuries',
SUM(CASE WHEN dbo.Accident.AccidentDate BETWEEN @date_from AND @date_to THEN ISNULL(dbo.Accident.Fatalitites,0) ELSE 0 END) AS 'Fatalities'
INTO
#Accident
FROM
dbo.Accident RIGHT OUTER JOIN
dbo.Personnel ON dbo.Accident.Tpr = dbo.Personnel.PersonnelId
Thanks
Ernest
|
 |
 |
Edd
Posts: 547
|
| Posted: 05/12/2006, 8:55 PM |
|
Ernest,
Passing dates to stored procedures suck, having said that you will never know how your server is configured so you should adopt a "universal date approach".
What I do is I set the input parameter format to "YYYY-MM-DD" (the fail safe date format).
Then to pass dates to the stored procedure I change the source from a URL to an expression an put a function name in the parameter such as GetStartDate()
I then create a function in the events area called GetStartDate() that returns a string format of the date "YYYY-MM-DD".
Eg.
function GetStartDate()
Dim MyDate
MyDate = CCGetParam("s_StartDate", Date())
GetStartDate = CCFormatDate(MyDate ,array("yyyy","mm","dd"))
End Function
Do the same for the End Date
Hope this helps
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
Ernest
|
| Posted: 05/14/2006, 10:06 AM |
|
Thanks for the reply.... I have attempted to try your solution, but still getting error: "string data, right truncation".
I'm sure its something I'm not uderstanding, but thanks for trying to help...
|
|
|
|
marcwolf
Posts: 361
|
| Posted: 05/15/2006, 5:57 PM |
|
I recently had the same issue where I was callin an MS-Access report with dates.
I ended up just using a date number yyyyMMddhhmmss to repesent the date as a universal format and then formatted it as needed.
The other advantage is this this date can be sorted..
Unfortunalely its not suitable for calculation however one could use a julian date with a decimal section for times.
Just some ideas
Dave
_________________
' Coding Coding Coding
Keep Those Keyboards Coding.
Raw Code!!!!!!!
|
|
|
|
Benjamin Krajmalnik
|
| Posted: 05/25/2006, 2:45 PM |
|
Edd,
the failsafe is "yyyymmdd" (no dashes). If you use yyyy-xx-xx, the server
locale may come into play.
I saw this happen on a customer's MS SQL server in Australia.
I recoded everything to use yyyymmdd (no dashes) and problems went away.
|
|
|
|
Edd
Posts: 547
|
| Posted: 05/26/2006, 12:22 AM |
|
Benjamin
You are correct it was a typo error of mine that I missed the dashes. I have a standard rountine for may dates so I am a bit rusty on using "CCFormatDate" which I have always found to be clumsy.
Thanks
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
|
