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

Question

hi all,

I used a query in sql server 2008.

select sum(convert(float,score)) as 'LHSScore',username,designation,userID 
from LHSUPDATE where CONVERT(date,startdate) >=CONVERT(date, '1/1/2013') 
and CONVERT(date,endDate) <=CONVERT(date, '12/31/2013') and designation in('P','PA','A') 
and score is not null group by 
username,designation,userID order by LHSScore desc

while excuting this query i am getting 

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

can anyone please help to resolve this.

Thanks,

Raji

Answer 1

What are the datatypes of the various columns involved with your convert statements.  If they are char or some sort of numeric, what is the format of the values that populate those columns (and are ALL of the values consistent in their format)?

Lastly, it is better to use a string format in your date literals that is not subject to any interpretation vagueness.  One such format is YYYYMMDD (e.g., Jan 1 2012 would be '20130101').  More info below:

Tibor - ultimate datetime guide

Answer 2

you are trying to convert string data type into Datetime. SQL server database engine is facing issue in conversion. You string data should in format that SQL server can recognize them as DD MM YYYY. Share some sample data for more understanding.

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answerand Vote as Helpfulon posts that help you. This can be beneficial to other community members reading the thread.

Answer 3

some sample data :

UserID	267085
UserName	raji
Designation	PA
CourseName	Goal Setting
CourseStatus	Completed
Score	0.06
LearningMode	E learning
StartDate	1/20/2013
EndDate	1/24/2013

Answer 4

CREATE TABLE [dbo].[LHSUpdate](
[UserID] [varchar](100) NULL,
[UserName] [varchar](100) NULL,
[Designation] [varchar](100) NULL,
[CourseName] [varchar](500) NULL,
[CourseStatus] [varchar](100) NULL,
[Score] [varchar](100) NULL,
[LearningMode] [varchar](100) NULL,
[StartDate] [varchar](20) NULL,
[EndDate] [varchar](20) NULL
) ON [PRIMARY]

Answer 5

The usefulness of responses is directly related to the amount of effort you make in posting useful and relevent information.  The error is quite specific - apparently you have values in StartDate or EndDate that cannot be converted.  As has been suggested, your query will fail if there are values that cannot be converted.  I suggest that you start working on a plan to either correct the data or avoid the bad data.  The link below will help you understand the issues you are facing - but only if you take the time to read it.

ultimate guide to datetime