Solved

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

Posted on 2014-12-10
19
21 Views
Last Modified: 2016-06-16
This stored procedure was working fine, then started throwing this error.  It's being used as the main query for an SSRS report.  If I put single quotes around the date I select, I get the error, but also see that there are results.  If I don't use the single quotes, the query runs and I get no results.  As you can see, the parameter is being passed as datetime, converted to varchar and then cast as char.  I didn't write the original query, but didn't change anything that had to do with this part.  I can't seem to find the reason for it not working now.
SQLQuery2.sql
0
Comment
Question by:Sherry
  • 7
  • 7
  • 3
  • +1
19 Comments
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
it is not a stored procedure... it is bunch of sql statements put together...
what code/line is giving error? what is error message? which line?
how do you call/run this?
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
try this

@CalloutStartDate AS DATETIME  = convert (date, '11/20/2014', 101) -- mm/dd/yyyy

dont think it will make any difference, but you should use correct conversion...
0
 

Author Comment

by:Sherry
Comment Utility
Sorry, sent you the code that I was trying to just run.  Here's the procedure it's self.  It fails at the CAST in the final resultset.  It's called from the SSRS report.
Report-Offender-Attendance-Roster.sql
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
you mean here?

, CAST (R.CalloutDayMMDDYYYY AS CHAR(10)) + ' - ' + DATENAME(dw, R.CalloutDayMMDDYYYY) as Date_Name

change it to simple thing and check your data to find why it is failing

, CAST (R.CalloutDayMMDDYYYY AS CHAR(10)) as Date_Name

check data whether you see anything strange here...
0
 

Author Comment

by:Sherry
Comment Utility
Ok, will do.  Thanks
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
what error do you get exactly?

this one?

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I guess that error comes from DATETIME function not from cast... You are passing an invalid date to DATETIME function I guess, which you should be able to find with my suggestion above...
0
 

Author Comment

by:Sherry
Comment Utility
When I set the date as '11/01/2014' I get the following error ( I understand this is the wrong format.) -

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


When I set the date as 11/01/2014, for some reason, I no longer get any results.  And I should.  
I tried you suggestions, it didn't make any difference.
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
i dont see any problem with '11/01/2014' or '01/11/2014'
The problem is somewhere else...

did you try this?

, CAST (R.CalloutDayMMDDYYYY AS CHAR(10)) + ' - ' + DATENAME(dw, R.CalloutDayMMDDYYYY) as Date_Name
>>>>
, CAST (R.CalloutDayMMDDYYYY AS CHAR(10)) as Date_Name

and run your sp this way? do you get the same error?
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
CONVERT(VARCHAR(10), c.StartDateTime, 101) AS CalloutDayMMDDYYYY

this makes "NULL" then you are trying to convert 'NULL' to datename which gives error of course...

I suggested remove datename from your query, but I guess you never tried it...

issue is null value in callout.StartDateTime

CONVERT(VARCHAR(10), c.StartDateTime, 101) AS CalloutDayMMDDYYYY
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:Sherry
Comment Utility
So far, I've tried all of your suggestions.  None have worked.  I know the date is a good one.  I've check the table for the callouts and selected one and with that facility.  There should be results.  So, maybe I'm looking at this wrong.  I'll leave the date as it is.  The query runs, but for some other reason then, I get no results, when I should.
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
So far, I've tried all of your suggestions.  None have worked.

if your problem is here:

CAST (R.CalloutDayMMDDYYYY AS CHAR(10)) + ' - ' + DATENAME(dw, R.CalloutDayMMDDYYYY) as Date_Name

commenting that line out will solve the issue :) I mean no error...

then instead of that line try

null as Date_name

then

R.CalloutDayMMDDYYYY AS Date_Name

then try

CAST (R.CalloutDayMMDDYYYY AS CHAR(10)) AS Date_Name

all above should work... I believe your issue is null value in c.StartDateTime

add this to your query

c.StartDateTime is not null and try again with your original code...
0
 

Author Comment

by:Sherry
Comment Utility
Ok, will try it out.  I'm on my way to a meeting though, so will get back to you tomorrow.  Thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
LIne 239 of Report-Offender-Attendance-Roster.sql
,      CONVERT(VARCHAR(10), c.StartDateTime, 101) AS CalloutDayMMDDYYYY

DON'T do that conversion at that point, leave it as a datetime value, just do this:
,      c.StartDateTime AS CalloutDayMMDDYYYY

Then, later, lines 301 and 302, do this:

      , CAST (R.CalloutDayMMDDYYYY AS CHAR(10))  AS CalloutDayMMDDYYYY
      , CAST (R.CalloutDayMMDDYYYY AS CHAR(10)) + ' - ' + DATENAME(dw, R.CalloutDayMMDDYYYY) as Date_Name


(The current problem is using DATENAME() where you have already converted that field to a string. )
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
btw: You may want to adopt a different alias for that field, e.g.

line 239
,      c.StartDateTime AS CalloutDateTime

lines 301-302
     , CAST (R.CalloutDateTime AS CHAR(10))  AS CalloutDayMMDDYYYY
      , CAST (R.CalloutDateTime AS CHAR(10)) + ' - ' + DATENAME(dw, R.CalloutDateTime) as Date_Name
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Do you know since when it started to get error? Did you migrate the database or updated the Operating System?
It can be a change in the date format and for that there's a simple test that you can do, that is to change CHAR(10) to CHAR(11):
(...) CAST (@CalloutStartDate AS CHAR(11)) + ' - ' + DATENAME(dw, @CalloutStartDate) (...)

Open in new window

0
 

Author Comment

by:Sherry
Comment Utility
Changing to Char(11) didn't matter.  I have found that the dropdown for the @SortBy that is in the final portion of the query, shows that it has two fields ( ID int, Description varchar(50)).  @SortBy in the query is tinyint.  Which is ok, it matches up with the ID.  But the sorting by Time in the dropdown would then be varchar(50) and in the query, it uses the CalloutDateTime, which is still datetime.  I tried to cast the CalloutDateTime as Char(10) but it still fails.

I've updated my query and am attaching it.  I'm also attaching the Dropdown so that you can see what I'm talking about.  If I comment out the @sortby at the bottom of the final portion of the query, the query runs fine.
SQLQuery2.sql
DropdownList-SortBy.sql
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>"I tried to cast the CalloutDateTime as Char(10) but it still fails."
what is the failure?
are you still getting a conversion error?

Could you provide the exact and complete error message please.
0
 

Accepted Solution

by:
Sherry earned 0 total points
Comment Utility
I got this straightened out.  The sort option/dropdow was written so that it needed a string not int.  So I put single quotes on the 0's in the sort statements at the end of the query and all's good.
Thank you for your help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

728 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

9 Experts available now in Live!

Get 1:1 Help Now