Error : Conversion failed when converting date and

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 2008 Forums

Transact-SQL (2008)

Error : Conversion failed when converting date and

Author

Topic

ranganathanmca
Starting Member

15 Posts

Posted - 2011-06-23 : 07:19:15

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

ranganathan palanisamy

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-23 : 07:25:15

Cast or convert to string, not the other way around, for example:


...
WHERE E.EventDate >=' + cast(@EventDate as varchar(32)) + 'And E.Cancelled=0'

That aside, it looks like you may not need the dynamic sql in this case. It is much safer (and probably more efficient) if you can avoid dynamic sql.

ranganathanmca
Starting Member

15 Posts

Posted - 2011-06-23 : 07:29:31

quote:
Originally posted by sunitabeck

Cast or convert to string, not the other way around, for example:

...
WHERE E.EventDate >=' + cast(@EventDate as varchar(32)) + 'And E.Cancelled=0'
That aside, it looks like you may not need the dynamic sql in this case. It is much safer (and probably more efficient) if you can avoid dynamic sql.

Thanks for Reply

Still i am getting error

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 >=Jun 23 2011 12:00AMAnd E.Cancelled=0
Msg 105, Level 15, State 1, Line 6
Unclosed quotation mark after the character string ') 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 >=Jun 23 2011 12:00AMAnd E.Cancelled=0'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ') AS EventTypeName,
ISNULL(E_T.TotalTalents, 0) AS TotalTalents
FROM EventTypes AS ET
RIGHT OUTER JOIN EventMaster AS E ON '.

ranganathan palanisamy

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-23 : 07:43:23

Are you getting the same error? You probably need some spaces and single quotes around the datestring:


...
WHERE E.EventDate >= ''' + cast(@EventDate as varchar(32)) + ''' And E.Cancelled=0'

Look at the sql statement printed out in your print statement (print @strQuery) and see if that makes sense. You might even want to copy and paste it into a SSMS query window and parse it to see if it has any syntax errors.

ranganathanmca
Starting Member

15 Posts

Posted - 2011-06-23 : 08:09:02

Thanks for Reply

Still i am getting the error

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'Nov'.
Msg 105, Level 15, State 1, Line 13
Unclosed quotation mark after the character string ' And E.Cancelled=0'.

Argument :

exec GetUpcomingEventsForSchedulings '2009-11-05 00:00:00.000','',''

Table format:

EventDate -- > datetime

Please give share the suggestion to reslove this error

quote:
Originally posted by sunitabeck

Are you getting the same error? You probably need some spaces and single quotes around the datestring:

...
WHERE E.EventDate >= ''' + cast(@EventDate as varchar(32)) + ''' And E.Cancelled=0'
Look at the sql statement printed out in your print statement (print @strQuery) and see if that makes sense. You might even want to copy and paste it into a SSMS query window and parse it to see if it has any syntax errors.

ranganathan palanisamy

ranganathanmca
Starting Member

15 Posts

Posted - 2011-06-23 : 08:20:31

Thanks for Reply sunitabeck

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

quote:
Originally posted by ranganathanmca

Thanks for Reply

Still i am getting the error

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'Nov'.
Msg 105, Level 15, State 1, Line 13
Unclosed quotation mark after the character string ' And E.Cancelled=0'.

Argument :

exec GetUpcomingEventsForSchedulings '2009-11-05 00:00:00.000','',''

Table format:

EventDate -- > datetime

Please give share the suggestion to reslove this error

quote:
Originally posted by sunitabeck

Are you getting the same error? You probably need some spaces and single quotes around the datestring:

...
WHERE E.EventDate >= ''' + cast(@EventDate as varchar(32)) + ''' And E.Cancelled=0'
Look at the sql statement printed out in your print statement (print @strQuery) and see if that makes sense. You might even want to copy and paste it into a SSMS query window and parse it to see if it has any syntax errors.

ranganathan palanisamy

ranganathan palanisamy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources