Syntax error converting datetime from character string

Question

Post reply

Syntax error converting datetime from character string

Paul Roberts-233050

Grasshopper

Points: 22
More actions
May 16, 2005 at 6:06 am

#66114

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

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

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 !!!**

Paul Roberts-233050 Grasshopper Points: 22 More actions · Answer 2

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".

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

won't work either... he's not using dynamic sql :

from today's newsletter :

http://www.sqlservercentral.com/columnists/aGrinberg/thearrayinsqlserver2000.asp

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Sorry I thaught that the split function was mentioned in that article...

Read this instead :

Arrays and Lists in SQL Server

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

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

Rajesh-228994 SSC Veteran Points: 282 More actions · Answer 6

You'll need to use dynamic sql somewhat like this.

declare @dt varchar(50), @sql varchar(100)

select @dt = '''11-19-2004'',''12-27-2004'''

select @sql = 'select * from mytable where dtcol in (' + @dt +')'

exec(@sql)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

You don't need dynamic sql for this task, read up on the links we posted.