Solved

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

Posted on 2016-10-10
5
72 Views
Last Modified: 2016-10-13
    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
0
Comment
Question by:vbnetcoder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41837002
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
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41837077
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 41837107
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41838557
>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
 

Author Closing Comment

by:vbnetcoder
ID: 41842121
ty
0

Featured Post

 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question