[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2014-12-10
19
Medium Priority
?
121 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
[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
  • 7
  • 7
  • 3
  • +1
19 Comments
 
LVL 60

Expert Comment

by:HainKurt
ID: 40492234
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 60

Expert Comment

by:HainKurt
ID: 40492249
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
ID: 40492259
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 60

Expert Comment

by:HainKurt
ID: 40492277
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
ID: 40492283
Ok, will do.  Thanks
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 40492291
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
ID: 40492484
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 60

Expert Comment

by:HainKurt
ID: 40492544
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 60

Expert Comment

by:HainKurt
ID: 40492553
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
 

Author Comment

by:Sherry
ID: 40492569
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 60

Expert Comment

by:HainKurt
ID: 40492637
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
ID: 40492672
Ok, will try it out.  I'm on my way to a meeting though, so will get back to you tomorrow.  Thanks
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40492996
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 49

Expert Comment

by:PortletPaul
ID: 40492999
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 52

Expert Comment

by:Vitor Montalvão
ID: 40495986
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
ID: 40505148
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 49

Expert Comment

by:PortletPaul
ID: 40505735
>>"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
ID: 40508210
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

656 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