Solved

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

Posted on 2016-10-10
5
49 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 45

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 18

Expert Comment

by:Pawan Kumar Khowal
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:
ScottPletcher 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQl query 19 14
Auditing in Azure SQL Database 3 28
Delete Trigger in SQL Server2008R2 5 13
login and database user 3 22
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now