November 27, 2008 at 5:19 am
Hi,
I have two tables within the same database, both have several datetime columns in the format i.e. 27/11/2008 11:44:28.
A user defined function to get working minutes between two dates 'uses' data from these tables.
Up until a few days ago, this function worked fine on both tables, but now it will only accept the dates from one of the tables - the other results in the following error:
The conversion of a char data type to a datetime data type
results in an out-of-range datetime.
None of the tables have been changed, the udf has not been changed.
My question is, is there some sort of setting I am missing, the can change the datetime format of specific tables within a database?
Thanks,
jinx.
November 27, 2008 at 5:29 am
Jinx (11/27/2008)
Hi,I have two tables within the same database, both have several datetime columns in the format i.e. 27/11/2008 11:44:28.
A user defined function to get working minutes between two dates 'uses' data from these tables.
Up until a few days ago, this function worked fine on both tables, but now it will only accept the dates from one of the tables - the other results in the following error:
The conversion of a char data type to a datetime data type
results in an out-of-range datetime.
None of the tables have been changed, the udf has not been changed.
My question is, is there some sort of setting I am missing, the can change the datetime format of specific tables within a database?
Thanks,
jinx.
It seems that there is some date that is not in the correct format.
Use Isdate() function to check where is the invalid date in the table.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 27, 2008 at 5:49 am
I tried the isdate() function and all dates are fine. The function when used on the one table, fails, but is fine on another. Both table have their dates populated by a c# application that uses the getdate() sql function.
What seems to happen is that the one table is populated in UK format while the other is populated in US format..though when I look at the data in both, they are in UK format.
It's a bit difficult to explain 😛
Thanks anyway for the suggestion. I have a work around where I can create two seperate udf's that do the same thing yet look for a different datetime format - for these two different tables.
November 27, 2008 at 6:03 am
Moral of the story: Store dates in datetime columns, not as varchar.
Any possibility of fixing the tables?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 27, 2008 at 6:07 am
They are all stored as datetime. That is why this error is so confusing. The entire development team here is confused by this. 🙂
November 27, 2008 at 6:17 am
Then there's something wrong somewhere in the function.
Please can you post the table's definition (the one that's failing) as a create table, and the function's definition along with a couple of rows of sample data (as insert statements)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 27, 2008 at 8:15 am
Hi,
Problem solved! oddly enough if we run the udf using the sa account, it runs fine on all tables. Odd that we should get a data type error considering it is an account/domain issue. :S
Thanks for the help though.
Jinx.
November 27, 2008 at 8:33 am
That should not be possible. Running as sa is definitely not recommended. Too many security problems.
Can you please post the udf so that maybe someone can spot the root problem?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 27, 2008 at 8:42 am
The fact that we can run it as sa pointed us in the right direction - that this problem is caused by some customised active directory software we had installed. It has caused several other problems - this one was just odd becasue of the error. I would not continue to run as sa, but it was a starting point to find out what settings have been changed. Sorry I was not clear earlier.
here it the udf none the less:
CREATE function dbo.GetWorkingMinutes(@startdate datetime, @enddate datetime)
returns int
as
begin
declare @wmd int,
@startbase datetime,
@endbase datetime
select @startbase = convert(datetime, convert(char(2), datepart(d, @startdate)) + '-' +
convert(char(2), datepart(m, @startdate)) + '-' +
convert(char(4), datepart(yy, @startdate)) + ' 17:15:00.000')
select @endbase = convert(datetime, convert(char(2), datepart(d, @enddate)) + '-' +
convert(char(2), datepart(m, @enddate)) + '-' +
convert(char(4), datepart(yy, @enddate)) + ' 08:45:00.000')
if dbo.getworkingdays(@startdate, @enddate) = 0
if @startbase < @endbase
select @wmd = datediff(mi, @endbase, @enddate)
else
select @wmd = datediff(mi, @startdate, @enddate)
else
if (datediff(mi, @startdate, @startbase) < 0) and (datediff(mi, @endbase, @enddate) < 0)
select @wmd = 0
else if (datediff(mi, @startdate, @startbase) = 0)
select @wmd = ((dbo.getworkingdays(@startdate, @enddate) - 1) * 510) +
datediff(mi, @endbase, @enddate)
else if (datediff(mi, @startdate, @startbase) >= 0) and (datediff(mi, @endbase, @enddate) < 0)
select @wmd = ((dbo.getworkingdays(@startdate, @enddate) - 1) * 510) +
datediff(mi, @startdate, @startbase)
else select @wmd = ((dbo.getworkingdays(@startdate, @enddate) - 1) * 510) +
datediff(mi, @startdate, @startbase) +
datediff(mi, @endbase, @enddate)
return(@wmd)
end
Jinx.
November 27, 2008 at 8:54 am
select @startbase = convert(datetime, convert(char(2), datepart(d, @startdate)) + '-' +
convert(char(2), datepart(m, @startdate)) + '-' +
convert(char(4), datepart(yy, @startdate)) + ' 17:15:00.000')
select @endbase = convert(datetime, convert(char(2), datepart(d, @enddate)) + '-' +
convert(char(2), datepart(m, @enddate)) + '-' +
convert(char(4), datepart(yy, @enddate)) + ' 08:45:00.000')
This is very likely your problem. You're building up a string and then implicitly converting it back to a datetime. It will work sometimes and fail others depending on the dateformat. There's a much better way to remove the time from a date.
Try this rather
SELECT @startbase = DATEADD(mi, 15, DATEADD(hh,17, dateadd(dd, datediff(dd,0, @startdate),0)))
SELECT @endbase = DATEADD(mi, 45, DATEADD(hh,8, dateadd(dd, datediff(dd,0, @enddate),0)))
What does dbo.getworkingdays do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 27, 2008 at 9:11 am
That code you provided is much better! (I really feel like a bit of an idiot now :))
But unfortunately it produces exactly the same error as I had before - it also runs fine as sa, but not as anything else.
The getworkingdays function:
create function dbo.GetWorkingDays(@startdate datetime, @enddate datetime)
returns int
as
begin
declare @workingdays int
select @workingdays = count(*)
from tblBusinessCalendar
where btype = 1
and bdate between @startdate and @enddate
return(@workingdays)
end
Jinx.
November 27, 2008 at 9:30 am
Do you know where the error's occuring? The error message should include the line number. Can you run the function im Query analyser and post the full error message, including the error number, severity, state and line.
What's the definition of tblBusinessCalendar?
There's nothing else that I can see that's converting varchars to strings anywhere.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 27, 2008 at 9:41 am
You are 100% correct, there is nothing wrong with it, I had not replaced one name correctly with the new test udf I created using the code you provided. My bad! It works fine now as any user.
There is a reason I posted this in the newbies section :p. That will teach me to be so hasty.
Thank you very much for the help.
Jinx.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply