Conversion Failed When Converting Date And/or Time From Character String In Sql Server 2008

I have below SQL. UPDATE student_queues SET Deleted=0, last_accessed_by='raja', last_accessed_on=CONVERT(VARCHAR(24),'23-07-2014 09:37:00',113) WHERE std_id IN

Solution 1:

If you're trying to insert in to last_accessed_on, which is a DateTime2, then your issue is with the fact that you are converting it to a varchar in a format that SQL doesn't understand.

If you modify your code to this, it should work, note the format of your date has been changed to: YYYY-MM-DD hh:mm:ss:

UPDATE  student_queues 
SET  Deleted=0, 
     last_accessed_by='raja', 
     last_accessed_on=CONVERT(datetime2,'2014-07-23 09:37:00')
WHERE std_id IN ('2144-384-11564') AND reject_details='REJECT'

Or if you want to use CAST, replace with:

CAST('2014-07-23 09:37:00.000' AS datetime2)

This is using the SQL ISO Date Format.

Solution 2:

Seems like last_accessed_on, is a date time, and you are converting '23-07-2014 09:37:00' to a varchar. This would not work, and give you conversion errors. Try

last_accessed_on= convert(datetime,'23-07-2014 09:37:00', 103)  

I think you can avoid the cast though, and update with '23-07-2014 09:37:00'. It should work given that the format is correct.

Your query is not going to work because in last_accessed_on (which is DateTime2 type), you are trying to pass a Varchar value.

You query would be

UPDATE  student_queues SET  Deleted=0 ,  last_accessed_by='raja', last_accessed_on=convert(datetime,'23-07-2014 09:37:00', 103)  
 WHERE std_id IN ('2144-384-11564') AND reject_details='REJECT'

Solution 3:

DECLARE@FromDate DATETIME

SET@FromDate='Jan 10 2016 12:00AM'DECLARE@ToDate DATETIME
SET@ToDate='Jan 10 2017 12:00AM'DECLARE@Dynamic_Qry nvarchar(Max) =''SET@Dynamic_Qry='SELECT

(CONVERT(DATETIME,(SELECT 
     CASE WHEN (  ''IssueDate''   =''IssueDate'') THEN 
               EMP_DOCUMENT.ISSUE_DATE 
          WHEN (''IssueDate'' =''ExpiryDate'' ) THEN       
               EMP_DOCUMENT.EXPIRY_DATE ELSE EMP_DOCUMENT.APPROVED_ON END   
          CHEKDATE ), 101)  

)FROM CR.EMP_DOCUMENT  as EMP_DOCUMENT WHERE 1=1 

AND  (
      CONVERT(DATETIME,(SELECT 
        CASE WHEN (  ''IssueDate''   =''IssueDate'') THEN
                 EMP_DOCUMENT.ISSUE_DATE 
             WHEN (''IssueDate'' =''ExpiryDate'' ) THEN EMP_DOCUMENT.EXPIRY_DATE 
             ELSE EMP_DOCUMENT.APPROVED_ON END 
             CHEKDATE ), 101)  
) BETWEEN  '''+CONVERT(CHAR(10), @FromDate, 126) +'''  AND '''+CONVERT(CHAR(10),  @ToDate , 126
)
+'''  
'

print @Dynamic_Qry

EXEC(@Dynamic_Qry)