SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Error varchar to datetime
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rexx
Starting Member

7 Posts

Posted - 01/06/2007 :  18:40:24  Show Profile  Visit rexx's Homepage  Reply with Quote
I have been receiving the folowing error:

Error SQL :EXEC lin_SetLastTaxUpdate '2007-0-6 12:0:03', '2007-0-6 0:0:1', '2007-0-6 6:0:1', (0)
Error converting data type varchar to datetime

This has only started to occur at the beginning of the Month of January, it has been fine for 5 Months prior.
I am no SQL expert but I can see that the Month is being registered as a 0 instead of a 1. I have tried several of my own fixes and have searched for answers but have not come up with a solution.
Any assistence would be appreciated.

Below is the stored procedure:


/********************************************
lin_SetLastTaxUpdate

INPUT
@income datetime,
@tax datetime
OUTPUT

********************************************/
CREATE PROCEDURE [DBO].[lin_SetLastTaxUpdate]
(
@income datetime,
@tax datetime,
@manor datetime
)
AS

SET NOCOUNT ON

IF EXISTS(SELECT * FROM castle_tax)
BEGIN
UPDATE castle_tax SET income_update = @income, tax_change = @tax, manor_reset = @manor
END
ELSE
BEGIN
INSERT INTO castle_tax VALUES
( @income, @tax, @manor)

END




GO

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 01/06/2007 :  19:42:28  Show Profile  Visit jezemine's Homepage  Reply with Quote
Doesn't the error msg give you a hint?

EXEC lin_SetLastTaxUpdate '2007-0-6 12:0:03', '2007-0-6 0:0:1', '2007-0-6 6:0:1'

These dates aren't valid, as can be seen by executing this:

select cast('2007-0-6 12:0:03' as datetime)

Which month is month 0? there is no such date. Months are not 0-based in sql (but it looks like your app developer thinks they are. It's a good bet that all the data that was created with this sproc is off by a month) I assume you mean January by month 0. So you'll have to fix the app code that's calling this sproc to use 1-based months. also days are 1-based. years of course as well. but it looks like you already got that part right. Unless of course by 2007 you really mean what everybody else calls 2008.

also each part of the date other than the year ought to have two digits.

try this:

EXEC lin_SetLastTaxUpdate '2007-01-06 12:00:03', '2007-01-06 00:00:01', '2007-01-06 06:00:01'


www.elsasoft.org

Edited by - jezemine on 01/06/2007 19:55:03
Go to Top of Page

rexx
Starting Member

7 Posts

Posted - 01/06/2007 :  20:25:21  Show Profile  Visit rexx's Homepage  Reply with Quote
Thanks for your help Jezemine.
Yes as you can tell Im new to sql but I have learnt many things over the past few Months, and I will continue to learn, I guess we all start at the beginning.
You are correct , all the data is off by 1 Month but this is not urgent for me to change, I just need to set it right so the continueing Months will be correct.
A little hint on repairing the application code would be apppreciated, would I be correct in assuming it is in the castle_tax table setup somewhere.

Thanks
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 01/06/2007 :  20:58:33  Show Profile  Visit jezemine's Homepage  Reply with Quote
by "app code" i mean whoever is calling this sproc. that could be an web page, an exe, a web service, anything. It could also be called by another sproc.

If you don't know who is calling this sproc, you'll need to find out before you can fix it. Can you ask the dba that owns this database who is calling it?


www.elsasoft.org
Go to Top of Page

rexx
Starting Member

7 Posts

Posted - 01/06/2007 :  21:18:15  Show Profile  Visit rexx's Homepage  Reply with Quote
Unfortunetly I dont no the dba who created the Data Base.
I would say it is another sproc that is calling this as it occurs at the same time everyday, and no other outside applications have access to my server.
The data base is used for an online game known as Lineage 2.
Is there another way to find out which sproc is calling it or is searching 1 by 1 the only way.

Thanks for your time
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 01/06/2007 :  21:27:23  Show Profile  Visit jezemine's Homepage  Reply with Quote
If you know that it's another sproc, that's relatively easy. You can use this code from robvolk. it will search
syscomments for any string. In your case you are looking for the string "lin_SetLastTaxUpdate"

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32319




www.elsasoft.org
Go to Top of Page

rexx
Starting Member

7 Posts

Posted - 01/06/2007 :  22:33:35  Show Profile  Visit rexx's Homepage  Reply with Quote
Its seems I was wrong , I tried all data bases with that code and only found the original sproc. But thanks for link to the code.
The next file would be the ai.obj, I will start searching there, but the file is huge and it could take some time.

Thanks again
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 01/07/2007 :  00:12:40  Show Profile  Visit jezemine's Homepage  Reply with Quote
not sure what kind of system you are working on, but in windows, usually an obj file is compiled code, which gets combined with other obj files by something called a linker to create a dll or exe.

obj files are usually compiled from c or c++ source files. unless you have the source files, you can't fix bugs in an obj file successfully unless you a some kind of uber-hacker. If the bug is in an obj file, you need to fix the bug the source code that generated it, and then recompile.



www.elsasoft.org
Go to Top of Page

rexx
Starting Member

7 Posts

Posted - 01/07/2007 :  02:15:10  Show Profile  Visit rexx's Homepage  Reply with Quote
The files are 64bit using windows 2003 64bit server.
This is going to be like finding a needle in a hay stack, I do have a decompiler for the ai.obj but have not had to use it. Not even sure if that is where the source is as there are a few dll's in the folder and I guess it could also be located in the main exe.
Its a shame the sproc does not make reference to the file that is calling it.

Thanks anyway
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 01/07/2007 :  02:46:12  Show Profile  Reply with Quote
"Its a shame the sproc does not make reference to the file that is calling it."

You may be able to use SQL Profiler to see what calls that Sproc over the next day or two.

Watch out when you repair the "caller" that all data up to that point will be "off-by-one-month", and data after won't, because you will need to add one month to all existing dates.

I would have expected off-by-one-month dates to have failed for incorrect last-day-of-month, so you may have already lost data, AND when you add one month you are going to lose more (or it will become inaccurate - adding one month to 31-Oct-2006 will give you 30-Nov-2006 with no error reported)

Kristen
Go to Top of Page

rexx
Starting Member

7 Posts

Posted - 01/07/2007 :  03:42:22  Show Profile  Visit rexx's Homepage  Reply with Quote
The sproc only updates three tables so it wont effect previous Months, the sproc overwrites the previous Months data.

I have never used SQL Profiler, do I create a new trace, leave it until the sproc is used and then some how check the data collected.
or is it possable to run the trace and manually use the sproc.
Thanks
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 01/07/2007 :  11:12:27  Show Profile  Reply with Quote
"I have never used SQL Profiler, do I create a new trace, leave it until the sproc is used and then some how check the data collected."

What would be best. If you run it manually it will tell you that YOU ran it, whereas what you actually need to know is the APPLICATION that runs it!

You can put Filters on SQL Profiler. Ideally filter to that Sproc name so that you don't have to wade through truck-loads of irrelevant stuff. You might THEN want to do one call manually to check that SQL Profiler is actually "catching" that specific SProc name.

Kristen
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 01/07/2007 :  12:12:00  Show Profile  Visit jezemine's Homepage  Reply with Quote
make sure to include HostName in the trace so you know what machine is making the call.


www.elsasoft.org
Go to Top of Page

rexx
Starting Member

7 Posts

Posted - 01/18/2007 :  04:20:12  Show Profile  Visit rexx's Homepage  Reply with Quote
Fixed with this from another forum, thanks for your help here.

ALTER PROCEDURE dbo.lin_SetLastTaxUpdate
(
@income varchar(100),
@tax varchar(100),
@manor varchar(100)
)
AS

declare @income_ datetime
declare @tax_ datetime
declare @manor_ datetime

set @income =left(@income,5) + cast((cast(replace(substring(@income,6,2),'-','') as int) + 1) as varchar) + substring(@income, charindex ('-', @income, 6),100)
set @tax =left(@tax,5) + cast((cast(replace(substring(@tax,6,2),'-','') as int) + 1) as varchar) + substring(@tax, charindex ('-', @tax, 6),100)
set @manor =left(@manor,5) + cast((cast(replace(substring(@manor,6,2),'-','') as int) + 1) as varchar) + substring(@tax, charindex ('-', @tax, 6),100)

set @income_ = convert(datetime, @income)
set @tax_ = convert(datetime, @tax)
set @manor_ = convert(datetime, @manor)


SET NOCOUNT ON
IF EXISTS(SELECT * FROM castle_tax)
BEGIN
UPDATE castle_tax SET income_update = @income_, tax_change = @tax_, manor_reset = @manor_
END
ELSE
BEGIN
INSERT INTO castle_tax VALUES
( @income_, @tax_, @manor_)

END
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000