Tuesday, February 19, 2013 1:15 PM
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.
Tuesday, February 19, 2013 2:33 PM
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:
Tuesday, February 19, 2013 9:13 PMyou 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.
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.
- Edited by RohitGargMicrosoft Community Contributor Tuesday, February 19, 2013 9:13 PM
Wednesday, February 20, 2013 3:33 AM
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
- Edited by rajilakshman Wednesday, February 20, 2013 3:36 AM
Wednesday, February 20, 2013 7:09 AM
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]
Wednesday, February 20, 2013 2:14 PM
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.
- Marked As Answer by Iric WenModerator Thursday, February 28, 2013 8:38 AM