Error varchar to datetime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Error varchar to datetime

Author

Topic

rexx
Starting Member

7 Posts

Posted - 2007-01-06 : 18:40:24

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
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-06 : 19:42:28

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

rexx
Starting Member

7 Posts

Posted - 2007-01-06 : 20:25:21

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

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-06 : 20:58:33

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

rexx
Starting Member

7 Posts

Posted - 2007-01-06 : 21:18:15

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

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-06 : 21:27:23

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

rexx
Starting Member

7 Posts

Posted - 2007-01-06 : 22:33:35

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

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-07 : 00:12:40

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

rexx
Starting Member

7 Posts

Posted - 2007-01-07 : 02:15:10

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

Kristen
Test

22859 Posts

Posted - 2007-01-07 : 02:46:12

"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

rexx
Starting Member

7 Posts

Posted - 2007-01-07 : 03:42:22

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

Kristen
Test

22859 Posts

Posted - 2007-01-07 : 11:12:27

"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

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-07 : 12:12:00

make sure to include HostName in the trace so you know what machine is making the call.

www.elsasoft.org

rexx
Starting Member

7 Posts

Posted - 2007-01-18 : 04:20:12

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources