Solved

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

Posted on 2016-10-10
5
61 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
5 Comments
 
LVL 47

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 28

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 65

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

832 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