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

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
SherryDeveloperAsked:
Who is Participating?
 
SherryConnect With a Mentor DeveloperAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SherryDeveloperAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
SherryDeveloperAuthor Commented:
Ok, will do.  Thanks
0
 
HainKurtSr. System AnalystCommented:
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
 
SherryDeveloperAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
SherryDeveloperAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
SherryDeveloperAuthor Commented:
Ok, will try it out.  I'm on my way to a meeting though, so will get back to you tomorrow.  Thanks
0
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
SherryDeveloperAuthor Commented:
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
 
PortletPaulfreelancerCommented:
>>"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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.