June 2, 2009 at 7:56 pm
Hi guys,
For some reason a script has recently failed. The problem is that I am getting:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
this is on the following query:
select distinct s.emailaddress, s.fname, s.sname from so_history h
inner join so_main m on m.invoiceid = h.invoiceid
inner join hr_staff s on s.staffid = m.staffid and s.activelogin = 1
inner join sy_branch sy on sy.branchid = m.branchid and sy.description = 'Consignment'
inner join so_customsettingvalues csv on csv.invoiceid = m.invoiceid and csv.contents is not null and csv.contents <> '' and csv.settingid = '3314326B978443D0A1B7'
where convert(datetime,replace(right(csv.contents,7),'/','/' + left(csv.contents,3))) >= getdate()
and convert(datetime,replace(right(csv.contents,7),'/','/' + left(csv.contents,3))) < dateadd(dd,5,getdate())
and m.status = 2
and m.creditnote <> 1
and s.emailaddress like '%@%'
I have looked at all of the dates returned by this and none are out of range - they all convert correctly when I return them as a column header like the following:
select distinct s.emailaddress, s.fname, s.sname, convert(datetime,replace(right(csv.contents,7),'/','/' + left(csv.contents,3))) from so_history h
inner join so_main m on m.invoiceid = h.invoiceid
inner join hr_staff s on s.staffid = m.staffid and s.activelogin = 1
inner join sy_branch sy on sy.branchid = m.branchid and sy.description = 'Consignment'
inner join so_customsettingvalues csv on csv.invoiceid = m.invoiceid and csv.contents is not null and csv.contents <> '' and csv.settingid = '3314326B978443D0A1B7'
where m.status = 2
and m.creditnote <> 1
and s.emailaddress like '%@%'
My question is: how are these being returned with the correct conversion in the first line of the select while when they are included in the where part it fails?
Thanks,
Danni
June 2, 2009 at 8:41 pm
The problem is that the error is being caused by some one or more of the rows that are filtered out by the other WHERE clauses. When you convert is in the SELECT list, which is done after the WHERE is applied, the conver() never sees those rows and no error is issued.
However, when the conver() is in the WHERE clause, SQL can execute it and the other WHERE clauses in any order that it wants to. In your case its doing the convert() first, so it sees the bad rows and you get the error. Moving the convert() around in the WHERE clause won't fix it, SQL can still decide to do it before the others.
Instead, you have to protect it behind a CASE expression:
select distinct s.emailaddress, s.fname, s.sname from so_history h
inner join so_main m on m.invoiceid = h.invoiceid
inner join hr_staff s on s.staffid = m.staffid and s.activelogin = 1
inner join sy_branch sy on sy.branchid = m.branchid and sy.description = 'Consignment'
inner join so_customsettingvalues csv on csv.invoiceid = m.invoiceid and csv.contents is not null and csv.contents '' and csv.settingid = '3314326B978443D0A1B7'
where (CASE WHEN m.status = 2
and m.creditnote 1
and s.emailaddress like '%@%'
THEN CASE WHEN convert(datetime,replace(right(csv.contents,7),'/','/' + left(csv.contents,3))) >= getdate()
and convert(datetime,replace(right(csv.contents,7),'/','/' + left(csv.contents,3))) < dateadd(dd,5,getdate())
THEN 1 ELSE 0 END
ELSE 0 END) = 1
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 2, 2009 at 10:11 pm
Hi,
Thanks for the help. Unfortunately it seems that this worked fro parts of the script (multiple reports are run) but for one last bit it didn't.
Essentially it is:
select * from so_main m
inner join so_customsettingvalues csv on csv.invoiceid = m.invoiceid and csv.contents is not null and csv.contents '' and csv.settingid = '3314326B978443D0A1B7'
inner join db_main d on d.debtorid = m.debtorid and d.accountno = 'T20683'
inner join sy_branch sy on sy.branchid = m.branchid
where (CASE WHEN m.status 2 and THEN
CASE WHEN convert(datetime,replace(right(csv.contents,7),'/','/' + left(csv.contents,3))) <= getdate()
THEN 1 ELSE 0 END
ELSE 0 END) = 1
When I change this again to do the datetime conversion as a column returned there is no problem. I tried to use isdate() in the case selection but I still have a conversion failure. Am I using this wrong?
I did:
select * from so_main m
inner join so_customsettingvalues csv on csv.invoiceid = m.invoiceid and csv.contents is not null and csv.contents '' and csv.settingid = '3314326B978443D0A1B7'
inner join db_main d on d.debtorid = m.debtorid and d.accountno = 'T20683'
inner join sy_branch sy on sy.branchid = m.branchid
where (CASE WHEN m.status 2 and isdate(convert(datetime,replace(right(csv.contents,7),'/','/' + left(csv.contents,3)))) = 1 THEN
CASE WHEN convert(datetime,replace(right(csv.contents,7),'/','/' + left(csv.contents,3))) <= getdate()
THEN 1 ELSE 0 END
ELSE 0 END) = 1
June 2, 2009 at 11:26 pm
Hi,
I have a solution.
Due to the select working for the conversion, I ended up selecting into a #temptable, joining that on to the query and then using that in the where clause.
Thanks for your help with the case statement. Hope this helps anyone else that may run into it in the future.
June 3, 2009 at 12:05 am
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply