June 19, 2008 at 11:00 pm
Hi all,
Any Help will be greatly appreciated.....
I have a function written to extract date from a varchar field. The function is as follows:
create function dbo.dateOnly(@date varchar(20))
returns varchar(12)
as
begin
declare @myDate varchar(12)
if @date <> ''
set @myDate = Convert(Varchar(10),Convert(DateTime,substring(@date,1,8)),103)
else
set @myDate = ' '
return @myDate
end
this function works OK when i am using it in the select part of my query. however when i use this function in my where clause i get error message stating as follows
"Conversion failed when converting datetime from character string."
Part of the query is as follows
Select distinct
dbo.DateOnly(R2.evFieldValKey) as 'Date Due in ODG' ,dbo.DateOnly(R3.evFieldValKey) as 'Date Recieved in ODG'
from RecordCTE R1
Where R1.evFieldURI = '6'
and dbo.DateOnly(R2.evFieldValKey) <= '31/05/2008'
Many Thanks
Rad
June 20, 2008 at 12:06 am
Replace '31/05/2008' with '05/31/08'
June 20, 2008 at 6:03 am
I disagree -- at least nominally; the problem with this statement:
and dbo.DateOnly(R2.evFieldValKey) <= '31/05/2008'
is that you have wrapped the "R2.evFieldValKey" in the dbo.DateOnly() function. This makes this unsargeable and if there were an index on this field it would be unusable because of this. Now, if the '31/05/2008' expression is not properly formatted you might be right; about changing around the format. I would also suggest that using '20080531' as the D/T specification will work despite any language or locale settings.
But I am afraid it might be worse still. "R2.evFieldValKey" looks like some kind of a generic EAV table key and might not be a dateTime datatype. In that case any indexing comments I just made probably don't matter anyway. Yuk
June 20, 2008 at 8:43 am
Don't use <= date + 23:59:59.997. Make it midnight (time 0) of the following day and just use <. More accurate that way. Also deals with the fact that in SQL 2008, there's a datetime2 data type that goes to greater accuracy than 1/300th second.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2008 at 8:44 am
On the original post, the error from the function is hapenning because of some string being fed into it, where substring(1,8) can't be converted to datetime. You can check for that in the function using the IsDate function.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 29, 2008 at 6:13 pm
thank you all for your valuble comments and contribution... it all helped me to diagnose the error i was making to query my database...
vey much appreciated.
Rad
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply