5

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.

Aruna
  • 11,959
  • 3
  • 28
  • 42
Rithik_Star
  • 651
  • 5
  • 14
  • 39

3 Answers3

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.

Tanner
  • 22,205
  • 9
  • 65
  • 83
  • 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) – Rithik_Star Jul 23 '14 at 10:24
  • 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. – Tanner Jul 23 '14 at 10:27
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'
Mez
  • 4,666
  • 4
  • 29
  • 57
  • Can you pls explain .I am getting confused – Rithik_Star Jul 23 '14 at 10:04
  • 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. – Mez Jul 23 '14 at 10:07
  • the last_accessed_on field is DATETIME2 Nullable field – Rithik_Star Jul 23 '14 at 10:10
  • Exactly, so you cannot pass a varchar value into the date time field. My answer above should have worked for you - correct? – Mez Jul 23 '14 at 10:13
  • Updated my answer as well. – Mez Jul 23 '14 at 10:15
  • how to apply this cast function in the above query .Can you please guide me.Because I want to convert the value in 113 or 120 format – Rithik_Star Jul 23 '14 at 10:16
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/57820/discussion-between-mez-and-user3669712). – Mez Jul 23 '14 at 10:19
  • @Mez Have you actually tried this: `select cast('23-07-2014 09:37:00' as datetime)`, as it doesn't work in SQL Server as it doesn't understand the format it's being passed in, hence my use of the ISO format in my answer. – Tanner Jul 23 '14 at 10:21
  • This works convert(datetime,'23-07-2014 09:37:00', 103) – Mez Jul 23 '14 at 10:24
1
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)
Praveen P K
  • 79
  • 5