Hi

I'm trying to select only July from show_held but I keep on getting the error message saying:

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

I get error message after I write this code:

AND		show.show_held = '&July&'

As you can see from the below code, How do I select July from times_held_in_July?

thank you

	SELECT		event_name,
			DATENAME (MONTH, show_held) AS times_held_in_July
	FROM		event,
			show
	WHERE		event.show_id = show.show_id

-------------------------------------------------------------
Result:

event_name      times_held_in_July
Dressage	July
Jumping	        July
Led in	        July
Led in	        September
Led in	        May
Dressage	April
Dressage	July
Flag and Pole	July

	SELECT		event_name,
		        DATENAME (MONTH, show_held) AS  times_held_in_July

	FROM		event,
			show
	WHERE		event.show_id = show.show_id
	AND		show.show_held = '&July&'

------------------------------------------------------------
Result:

Msg 241, Level 16, State 1, Line 24
Conversion failed when converting date and/or time from character string.

Do you want to use particular year? The simple way is to use: (for 2015)

select e.event_name, sh.show_held

from event e inner join show sh on e.show_id = sh.show_id

where sh.show_held >='20150701' and sh.show_held < '20150801'

Hi H.Kim,

According to your query, it seems that the show_held column is a date data type column in show table. Because the date part for DATENAME function is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value.

If in this scenario, we cannot convert date from character string with the statement:
show.show_held = '&July&'

To fix this issue, please change your original query to like below:

SELECT  event_name,
          DATENAME (MONTH, show_held) AS  times_held_in_July

 FROM  event,
   show
 WHERE  event.show_id = show.show_id
 AND  datename(month,show.show_held) = 'July'

Reference:
DATENAME (Transact-SQL)

Thanks,
Katherine Xiong

Thank you so much it solved the problem straight away!

Nope they were in different years but thank you for your help now I know another way of solving other problems!

Result of the Datename() function is dependent on the SET LANGUAGE. You better use DATEPART(month, show_held) = 7

Also, using function on the column will make the query non-sargable, so it would not be able to use index on the date column.

Thank you!