January 24, 2008 at 1:28 pm
I've been asked to look at a lengthy 600 line procedure to transfer data from 3 tables to a table for reporting purposes doing a data cleanup on the way. The code is failing at this point and I can't work out whats wrong or even what this code is doing.
, isnull(convert(datetime,tfd.startoitmonth), convert(datetime, '9999-12-31')) as [OITdate]
, isnull(convert(datetime,tfd.startmonth), convert(datetime, '9999-12-31')) as [InvoiceDate]
, isnull(convert(datetime,tfd.startoitmonth), convert(datetime, '9999-12-31')) as [ActualOITDate]
, isnull(convert(datetime,tfd.startmonth), convert(datetime, '9999-12-31')) as [ActualInvoiceDate]
Error message is
Server msg 242, Level 16, Stage 3 The conversion of char data type to a datetime data type resulted in an out of range datetime value
How on earth do you get line numbers while debugging? 'Error at line 46' isn't very meaningful. Can someone save me from going completely gaga.
January 24, 2008 at 1:42 pm
It means one (or more) of the rows of data you are converting to datetime from char isn't acceptable to the database.
Try selecting the columns referenced in those lines of code ("startoitmonth", etc.), as follows:
select startoitmonth
from (whatever table it's from)
where isdate(startoitmonth) = 0
Use each of the columns till you find the ones that are out of the normal range. Once you clean those up, or exclude them in your Where clause, the proc should run.
On the "Line 42" thing, in Management Studio (or Query Analyzer), Ctrl+G will take you to the line number you're looking for. Watch out, because the error message ignore anything before the heading of the proc in the window you've got.
For example:
set ansi_nulls on
go
set quoted_identifiers on
go
create proc MyProc
as
select 1
If there was an error in the line "select 1", it would say the error was in line 3, but if you use Ctrl+G, you need to go to line 7, because of the 4 lines of "set ansi_nulls..." and so on before the "create proc...". So, to make it easier on yourself, delete the lines above "create" when you're debugging.
There might be better ways to do this (the various SQL editing tools from RedGate, ApexSQL, etc., all have better debugging tools), but that's the one I know of in Management Studio/Query Analyzer.
- 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
January 25, 2008 at 8:07 am
To see Line Numbers in SSMS, go to Tools -> Options -> Text Editor -> All Languages -> General and Plain Text -> General. To the right and near the bottom you'll see Display. Underneath that is a check box so you can start seeing Line Numbers.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply