Conversion failed when converting date and/or time from character string.

Question

Post reply

Conversion failed when converting date and/or time from character string.

ranganathanmca

SSC Eights!

Points: 920
More actions
June 23, 2011 at 5:22 am

#243256

Hi Guys.,
stored Procedure :
USE [TMSLocal]
GO
/****** Object: StoredProcedure [dbo].[GetUpcomingEventsForSchedulings] Script Date: 06/23/2011 15:43:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetUpcomingEventsForSchedulings]
@EventDate DATETIME,
@ExcludeBrands VARCHAR(225),
@ExcludeEventTypes VARCHAR(225)
AS
BEGIN
DECLARE @strQuery VARCHAR(MAX)
SET @strQuery = 'SELECT E.EventID, isnull(E.EventName,'') as EventName,
E.EventDate, E.venue,
E.VenueCity, E.VenueState, E.VenueCountry, E.EventStartTime, E.OnSaleDate,
E.SalesGross, E.TicketUnits, E.EventCapacity, E.Milage, E.BrandID,
ISNULL(B.BrandName, '') AS BrandName, E.EventType, E.Published,
ISNULL(ET.EventType, '') AS EventTypeName,
ISNULL(E_T.TotalTalents, 0) AS TotalTalents
FROM EventTypes AS ET
RIGHT OUTER JOIN EventMaster AS E ON ET.EventTypeID = E.EventType
LEFT OUTER JOIN Brands AS B ON E.BrandID = B.BrandID
LEFT OUTER JOIN (SELECT EventID, Count(TalentID) as TotalTalents
FROM event_Talent Group By EventID) E_T ON E_T.EventID = E.EventID
WHERE E.EventDate >=' + CONVERT(DATETIME, @EventDate) + 'And E.Cancelled=0'
IF(@ExcludeBrands <> '')
BEGIN
SET @strQuery = @strQuery + ' AND E.BrandID NOT IN (' + @ExcludeBrands + ')'
END
IF(@ExcludeEventTypes <> '')
BEGIN
SET @strQuery = @strQuery + ' AND E.EventType NOT IN (' + @ExcludeEventTypes + ')'
END
print @strQuery
EXEC(@strQuery)
END
Input Parameter :
exec GetUpcomingEventsForSchedulings '2009-11-02 00:00:00.000','',''
Error :
Msg 241, Level 16, State 1, Procedure GetUpcomingEventsForSchedulings, Line 10
Conversion failed when converting date and/or time from character string.
Please give me the solution for this problem
Thanks

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

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

ranganathanmca SSC Eights! Points: 920 More actions · Answer 1

Thanks

I got solution because single quots problem ,

'SELECT E.EventID, isnull(E.EventName,'''') as EventName,

E.EventDate, E.venue,

E.VenueCity, E.VenueState, E.VenueCountry, E.EventStartTime, E.OnSaleDate,

E.SalesGross, E.TicketUnits, E.EventCapacity, E.Milage, E.BrandID,

ISNULL(B.BrandName, '''') AS BrandName, E.EventType, E.Published,

ISNULL(ET.EventType, '''') AS EventTypeName,

ISNULL(E_T.TotalTalents, 0) AS TotalTalents

FROM EventTypes AS ET

RIGHT OUTER JOIN EventMaster AS E ON ET.EventTypeID = E.EventType

LEFT OUTER JOIN Brands AS B ON E.BrandID = B.BrandID

LEFT OUTER JOIN (SELECT EventID, Count(TalentID) as TotalTalents

FROM event_Talent Group By EventID) E_T ON E_T.EventID = E.EventID

missing single quotes ( Bold,Underline)

Thanks for great Support to slove the problem

thanks