error while using a cast as datetime in query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

error while using a cast as datetime in query

Author

Topic

torpkevuk
Starting Member

6 Posts

Posted - 2007-05-14 : 12:00:22

Hi all, I'm new to the forums so I apologise if this is in the wrong place (and if I'm meant to be using any tags around the sql.. sorry.. I don't know what they are yet)

I'm using SQL Server 2000 reporting services, and I have a query I've tested in the SQL Query Analyzer and I'm pulling back correct data, I put that SQL into reporting services and I get the same data returned to me, but when I change one of the values to a parameter (in this case @FromDate) I get an error saying

Line 1: Incorrect syntax near ')'.

Hope someone can point me in the right direction as I'm stumped. Thanks!

Here's my SQL (with regular data)

SELECT DISTINCT
IT_Incident.Incident_id, IT_Incident.Process_id, IT_Incident.Status, IT_Incident.Profit_Center_id,
IT_Incident.Building_id, CONVERT(varchar, IT_Incident.Create_Date, 101) AS Create_Date, IT_Incident.Customer_Name,
IT_Incident.Claim_Amt, IT_Incident.Claim_Paid,
Data_Warehouse.dbo.V_DW_Emp_Org_Chart_PC_Bldg.Address1 + ', ' + Data_Warehouse.dbo.V_DW_Emp_Org_Chart_PC_Bldg.City + ', ' + Data_Warehouse.dbo.V_DW_Emp_Org_Chart_PC_Bldg.State AS Location, Data_Warehouse.dbo.V_DW_Emp_Org_Chart_PC_Bldg.Profit_Center_Name, IT_Incident.Discovery_Method
FROM IT_Incident INNER JOIN
Data_Warehouse.dbo.V_DW_Emp_Org_Chart_PC_Bldg ON
IT_Incident.Building_id = Data_Warehouse.dbo.V_DW_Emp_Org_Chart_PC_Bldg.Building_id AND
IT_Incident.Profit_Center_id = Data_Warehouse.dbo.V_DW_Emp_Org_Chart_PC_Bldg.Profit_Center_id
WHERE
(IT_Incident.Process_id = 'RAS_WHSE')
AND (IT_Incident.Discovery_Method = 'Customer')
AND (IT_Incident.Status <> 'VOID')
AND (Data_Warehouse.dbo.V_DW_Emp_Org_Chart_PC_Bldg.Org_Level_id LIKE '%')
AND (CAST(CONVERT(varchar,IT_Incident.Create_Date, 112) AS DATETIME) >= CAST(CONVERT(varchar,'05/11/2007', 112) AS DATETIME)
AND CAST(CONVERT(varchar,IT_Incident.Create_Date, 112) AS DATETIME) <= CAST(CONVERT(varchar,'05/14/2007', 112) AS DATETIME))
ORDER BY
Data_Warehouse.dbo.V_DW_Emp_Org_Chart_PC_Bldg.Profit_Center_Name, CONVERT(varchar, IT_Incident.Create_Date, 101)

and here it is with the parameters in:

SELECT DISTINCT
IT_Incident.Incident_id, IT_Incident.Process_id, IT_Incident.Status, IT_Incident.Profit_Center_id, IT_Incident.Building_id, CONVERT(varchar,
IT_Incident.Create_Date, 101) AS Create_Date, IT_Incident.Customer_Name, IT_Incident.Claim_Amt, IT_Incident.Claim_Paid,
Data_Warehouse.dbo.V_DW_Emp_Org_Chart_PC_Bldg.Address1 + ', ' + Data_Warehouse.dbo.V_DW_Emp_Org_Chart_PC_Bldg.City + ', ' + Data_Warehouse.dbo.V_DW_Emp_Org_Chart_PC_Bldg.State
AS Location, Data_Warehouse.dbo.V_DW_Emp_Org_Chart_PC_Bldg.Profit_Center_Name, IT_Incident.Discovery_Method
FROM IT_Incident INNER JOIN
Data_Warehouse.dbo.V_DW_Emp_Org_Chart_PC_Bldg ON
IT_Incident.Building_id = Data_Warehouse.dbo.V_DW_Emp_Org_Chart_PC_Bldg.Building_id AND
IT_Incident.Profit_Center_id = Data_Warehouse.dbo.V_DW_Emp_Org_Chart_PC_Bldg.Profit_Center_id
WHERE (IT_Incident.Process_id = 'RAS_WHSE') AND (IT_Incident.Discovery_Method = 'Customer') AND (IT_Incident.Status <> 'VOID') AND
(Data_Warehouse.dbo.V_DW_Emp_Org_Chart_PC_Bldg.Org_Level_id LIKE @OrgLevelID) AND (CAST(CONVERT(varchar, IT_Incident.Create_Date, 112)
AS DATETIME) >= CAST(CONVERT(varchar, @FromDate, 112) AS DATETIME)) AND (CAST(CONVERT(varchar, IT_Incident.Create_Date, 112)
AS DATETIME) <= CAST(CONVERT(varchar, @ToDate, 112) AS DATETIME))
ORDER BY Data_Warehouse.dbo.V_DW_Emp_Org_Chart_PC_Bldg.Profit_Center_Name, CONVERT(varchar, IT_Incident.Create_Date, 101)

(sorry about the poor formatting on the second one, my reporting services seems to do that automatically - wish it wouldn't :\)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 12:08:30

select * from IT_Incident
where isdate(Create_Date) = 0

Peter Larsson
Helsingborg, Sweden

torpkevuk
Starting Member

6 Posts

Posted - 2007-05-14 : 12:12:03

thanks for the quick reply, though I'm not really sure what you're solution here means.. but I did run it, it returned no rows

maybe I need to elaborate a little, my first sql works perfectly and returns 6 rows, my issue is that when I change the hand typed date '05/11/2007' for a parameter @FromDate I get an error within SQL Server Reporting services that wont even let me execute the sql

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 12:16:41

[code]SELECT DISTINCT it.Incident_id,
it.Process_id,
it.Status,
it.Profit_Center_id,
it.Building_id,
it.Create_Date,
it.Customer_Name,
it.Claim_Amt,
it.Claim_Paid,
eoc.Address1 + ', ' + eoc.City + ', ' + eoc.State AS Location,
eoc.Profit_Center_Name,
IT_Incident.Discovery_Method
FROM IT_Incident AS i
INNER JOIN Data_Warehouse.dbo.V_DW_Emp_Org_Chart_PC_Bldg AS eoc ON eoc.Building_id = it.Building_id AND eoc.Profit_Center_id = it.Profit_Center_id
WHERE it.Process_id = 'RAS_WHSE'
AND it.Discovery_Method = 'Customer'
AND it.Status <> 'VOID'
AND eoc.Org_Level_id LIKE @OrgLevelID
AND it.Create_Date >= DATEADD(DAY, DATEDIFF(DAY, 0, @FromDate), 0)
AND it.Create_Date < DATEADD(DAY, DATEDIFF(DAY, 0, @FromDate), 1)
ORDER BY eoc.Profit_Center_Name,
it.Create_Date[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 12:17:48

Always use correct data type, in this case DATETIME/SMALLDATETIME.
And always enter dates in universal (IDO) YYYYMMDD format.

Peter Larsson
Helsingborg, Sweden

torpkevuk
Starting Member

6 Posts

Posted - 2007-05-14 : 12:21:38

your new SQL statement worked perfectly and pulled in the exact same data as my old one, except it works in SQL server reporting services.. thanks a lot, I really appreciate it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources