Syntax error converting datetime from character string
-
I have written an ASP.NET Reporting Engine. At the moment it runs on a start date(@Date1) and an end date(@Date2). Everything works brilliantly. However I have intergrated the ability to choose the dates or combination of dates that I will access by passing the dates as a comma separated string.
When I bring the string into my stored procedure and amend the script to handle it I get this error, "Syntax error converting datetime from character string".
Can anyone help with this issue or suggest a different way of doing this function that would be great.
CODE:
-----
DECLARE @compSetTotalRooms Numeric
DECLARE @SellDate datetime
DECLARE @TotalRooms Numeric
DECLARE @MyTotalRooms Numeric
DECLARE @MyNumber Numeric
DECLARE @CompSetNumber Numeric
DECLARE @MarketPenetration Numeric
DECLARE @TotalCompSetHotels Numeric
DECLARE @MyRooms2Sell Numeric
DECLARE @Rooms2Sell Numeric
DECLARE @RoomsSold Numeric
DECLARE @Revenue Numeric
DECLARE @MyRoomsSold bigint
DECLARE @MyRoomsRevenue bigint
Declare @CompSetID Bigint
Declare @BusinessID Bigint
Declare @Date1 Datetime
Declare @Date2 Datetime
Declare @DateRange nvarchar(4000)
SET @CompSetID = 68
SET @BusinessID = 94
SET @Date1 = '01/03/2005'
SET @Date2 = '15/03/2005'
SET @DateRange = '03/05/2005,04/05/2005,05/05/2005,10/05/2005,11/05/2005,12/05/2005,17/05/2005,18/05/2005,19/05/2005,24/05/2005,25/05/2005,26/05/2005,31/05/2005'
/**/
--
CREATE TABLE #tmp001 (
SellDate DateTime,
MyNumber bigint,
CompSetNumber Bigint,
Penetration Float )
/* Get Total Rooms in You Competitive Set */
-- Get the amount of hotels in the Competitive Set
SELECT
@TotalCompSetHotels = COUNT(HotelSpecific.totalRooms)
FROM
CompetitiveSetDetails
INNER JOIN
HotelSpecific
ON
CompetitiveSetDetails.setBusinessID = HotelSpecific.BusinessID
GROUP BY
CompetitiveSetDetails.CompetitiveSetID
HAVING
(CompetitiveSetDetails.CompetitiveSetID = @CompSetID)
SELECT
@compSetTotalRooms = SUM(HotelSpecific.totalRooms)
FROM
CompetitiveSetDetails
INNER JOIN
HotelSpecific
ON
CompetitiveSetDetails.setBusinessID = HotelSpecific.BusinessID
GROUP BY
CompetitiveSetDetails.CompetitiveSetID
HAVING
(CompetitiveSetDetails.CompetitiveSetID = @CompSetID)
-- Get My Total Rooms
SELECT
@MyTotalRooms = totalRooms
FROM
HotelSpecific with (NoLock)
WHERE
(BusinessID = @BusinessID)
/* Get Rooms Sold Next 7 */
DECLARE Rooms2sell_Cursor CURSOR FOR
SELECT
convert(datetime, Rooms2Sell.SellDate, 102),
SUM(Rooms2Sell.Rooms2Sell) AS Rooms2Sell,
SUM(Rooms2Sell.RoomsSold) AS RoomsSold,
SUM(Rooms2Sell.TotalRoomsRevenueForSellDate) as TotalRoomsRevenue
FROM
CompetitiveSetDetails with (NoLock)
INNER JOIN
Rooms2Sell with (NoLock)
ON
CompetitiveSetDetails.setBusinessID = Rooms2Sell.BusinessID
GROUP BY
Rooms2Sell.SellDate,
CompetitiveSetDetails.CompetitiveSetID
HAVING
(CompetitiveSetDetails.CompetitiveSetID = @CompSetID)
AND
(Rooms2Sell.SellDate in (@DateRange))
OPEN Rooms2sell_Cursor
FETCH NEXT FROM Rooms2sell_Cursor
INTO @SellDate, @Rooms2Sell, @RoomsSold, @Revenue
WHILE @@FETCH_STATUS = 0
BEGIN
--Get My Other Details for Sell Date
SELECT
@MyRooms2Sell = Rooms2Sell,
@MyRoomsSold = RoomsSold,
@MyRoomsRevenue = TotalRoomsRevenueForSellDate
FROM
Rooms2Sell with (NoLOCK)
WHERE
(BusinessID = @BusinessID)
AND
(SellDate = @SellDate)
--SELECT @Revenue as Revenue, @MyRoomsRevenue as [My Revenue], @MyRoomsSold as [My Rooms Sold], @RoomsSold as [RoomsSold]
if (@MyRooms2Sell <> 0)
Begin
Set @MyNumber = ( @MyRoomsRevenue / @MyTotalRooms )
End
ELSE
Begin
Set @MyNumber = 0
End
if (@compSetTotalRooms <> 0)
Begin
Set @CompSetNumber = ( @Revenue / ( @compSetTotalRooms / @TotalCompSetHotels ) )
End
Else
Begin
Set @CompSetNumber = 0
End
if (@CompSetNumber <> 0 OR @MyNumber <> 0)
Begin
Set @MarketPenetration = ((100 / @CompSetNumber) * @MyNumber)
End
Else
Begin
Set @MarketPenetration = 0
End
Insert Into #tmp001 (SellDate,MyNumber,CompSetNumber,Penetration) Values (@SellDate, @MyNumber, @CompSetNumber, @MarketPenetration)
FETCH NEXT FROM Rooms2sell_Cursor
INTO @SellDate, @Rooms2Sell, @RoomsSold, @Revenue
END
CLOSE Rooms2sell_Cursor
DEALLOCATE Rooms2sell_Cursor
SELECT
*
FROM
#tmp001
Drop Table #tmp001
Thank you
-
Don't have time to go through whole script but you may want to try:
SET @DateRange = '03/05/2005','04/05/2005','05/05/2005' etc.....
**ASCII stupid question, get a stupid ANSI !!!** -
Unfortunately that wouldnt work because I am Using @DateRange with an "in".
The problem starts at the opening of the Cursor. This problem doesn't exist when I have @Date1 and @Date2 using a "Between".
-
won't work either... he's not using dynamic sql :
from today's newsletter :
http://www.sqlservercentral.com/columnists/aGrinberg/thearrayinsqlserver2000.asp
-
Sorry I thaught that the split function was mentioned in that article...
Read this instead :
-
Actually, the real problem here is a functionality that's not particularly suited to Transact SQL. You can't do WHERE myColumn IN ( @commaseparatedstringofvalues ) just like that...
Have a look here at Erland's excellent article on 'Arrays and Lists in SQL Server' - then you can decide where to go next with this. http://www.sommarskog.se/arrays-in-sql.html
/Kenneth
-
You don't need dynamic sql for this task, read up on the links we posted.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply