Conversion failed when converting date and/or time from character string in SQL SERVER 2008

Question

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 ('2144-384-11564') 
   AND reject_details='REJECT'

when I ran the above SQL the below exception has been throwed.

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

select CONVERT(VARCHAR(24),'23-07-2014 09:37:00',113) works on my machine, what version of sql server are you using? — Tanner
– Tanner, Commented Jul 23, 2014 at 9:59
@Tanner The last accessed_by column is VARCHAR(50) and Last_accessed_on is DATETIME2 — Rithik_Star
– Rithik_Star, Commented Jul 23, 2014 at 10:09
Possible duplicate: stackoverflow.com/questions/14119133/… — Jon Schneider
– Jon Schneider, Commented Aug 8, 2016 at 21:33

Tanner · Accepted Answer · 2014-07-23 10:25:32Z

8

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.

edited Jul 23, 2014 at 10:25

answered Jul 23, 2014 at 10:16

Tanner

22.8k9 gold badges70 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rithik_Star Over a year ago

Thank you somuch .It works fine with the date format change as YYYY-MM-DD . Also I didnt change the type from VARCHAR to datetime2.Its working fine with the VARCHAR(24)

Tanner Over a year ago

The issue is simply with your choice of date format and SQL's inability to understand it as a datetime. Updating to the ISO format allows SQL to understand it hence it worked with the varchar.

Mez · Accepted Answer · 2014-07-23 10:23:23Z

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'

edited Jul 23, 2014 at 10:23

answered Jul 23, 2014 at 10:01

Mez

4,7364 gold badges32 silver badges58 bronze badges

9 Comments

Rithik_Star Over a year ago

Can you pls explain .I am getting confused

Mez Over a year ago

Did the query work, when using cast('23-07-2014 09:37:00' as datetime)? If yes, last_accessed_on is of type DateTime, and in order for an update to work - you cannot pass a varchar type, into a field with another type such as datetime.

Rithik_Star Over a year ago

the last_accessed_on field is DATETIME2 Nullable field

Mez Over a year ago

Exactly, so you cannot pass a varchar value into the date time field. My answer above should have worked for you - correct?

Mez Over a year ago

Updated my answer as well.

|

Praveen P K · Accepted Answer · 2017-01-11 06:39:33Z

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)

Collectives™ on Stack Overflow

Conversion failed when converting date and/or time from character string in SQL SERVER 2008

3 Answers 3

2 Comments

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related