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.
This question is related to
sql-server
sql-server-2008-r2
date-conversion
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'
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)
Source: Stackoverflow.com