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

    UPDATE SEO
  SET [ReportingMonth] = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(ResponseText  AS DATETIME) ), 0) 
  FROM[Extract] SEI
  WHERE [Questionid] = '22'
   AND SEI.FirstName = SEO.FirstName
   AND SEI.LastName = SEO.LastName
    AND SEI.CreatedDateTime = SEO.CreatedDateTime
   )  
FROM [Extract] SEO

Open in new window




In the code ResponseText is a varchar field.  I am updating ReportingMonth to be the first day and the time to be all zeros
vbnetcoderAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
UPDATE SEO
SET [ReportingMonth] = (SELECT TOP (1) DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST(ResponseText  AS DATETIME) ), 0)
FROM [Extract] SEI
WHERE [Questionid] = '22'
 AND SEI.FirstName = SEO.FirstName
 AND SEI.LastName = SEO.LastName
 AND SEI.CreatedDateTime = SEO.CreatedDateTime
AND ISDATE(SEI.ResponseText) = 1
 )  
FROM [Extract] SEO
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to be sure if the ResponseText only has dates. The following select will return the records that aren't date:
SELECT *
FROM TableName
WHERE ISDATE(ResponseText)<>1

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Try this.. if will update only update if the column's value is DateTime.

--

UPDATE SEO
  SET [ReportingMonth] = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, CASE WHEN TRY_PARSE(ResponseText AS DATETIME) IS NOT NULL THEN CAST(ResponseText  AS DATETIME) END ), 0) 
  FROM [Extract] SEI
  WHERE [Questionid] = '22'
  AND SEI.FirstName = SEO.FirstName
  AND SEI.LastName = SEO.LastName
  AND SEI.CreatedDateTime = SEO.CreatedDateTime )  
FROM [Extract] SEO

--

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>In the code ResponseText is a varchar field.
>I am updating ReportingMonth to be the first day and the time to be all zeros
Okay, I'll bite.  Why are we accepting date values in a varchar column, and why isn't the UI validating these values to be date format?
0
 
vbnetcoderAuthor Commented:
ty
0
All Courses

From novice to tech pro — start learning today.