Possible bug in T-Sql or Odbc Driver? Date time conversion to int...

microsoft.public.sqlserver.programming

30 Jun 2005 2:57 PM

certolnut

Hi All

I have a code block which works and looks like this...

SELECT      i.ItemNumber
, i.ItemDescription
,h.[Year]
,d.OnHandQuantity
,h.IssuedQuantity1 + h.ShippedQuantity1 as January
FROM         dbo.FS_Item i INNER JOIN
                      dbo.FS_ItemData d ON i.ItemKey = d .ItemDataKey INNER
JOIN
                      dbo.FS_InventoryHistoryYear h ON i.ItemKey = h.ItemKey
WHERE h.[Year] >= Year(@BeginDate)

However when I add a liitle line of logic to my WHERE statement, the query
blows up...

SELECT      i.ItemNumber
, i.ItemDescription
,h.[Year]
,d.OnHandQuantity
,h.IssuedQuantity1 + h.ShippedQuantity1 as January
FROM         dbo.FS_Item i INNER JOIN
                      dbo.FS_ItemData d ON i.ItemKey = d .ItemDataKey INNER
JOIN
                      dbo.FS_InventoryHistoryYear h ON i.ItemKey = h.ItemKey
WHERE h.[Year] >= Year(@BeginDate)
AND
h.[Year] <= Year(@EndDate)

The error message I get is:

Server: Msg 245, Level 16, State 1, Procedure SlowMovingInventorySP, Line 16
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
varchar value 'CF07' to a column of data type int.

I've tried several different iterations, converted @EndDate to a datetime
value, used the BETWEEN statement and I still get the same error..

This looks like a bug.. Anyone have any suggestions???

30 Jun 2005 3:14 PM

Ray

What is the datatype for the Year field in this table?
dbo.FS_InventoryHistoryYear h
I don't think its a datetime value, and I'll bet query
select *
From dbo.FS_InventoryHistoryYear h
where [Year] = 'CF07'
Returns a result. But I don't know because you need to post the table
schema, parameter declarations, and how you are setting them for anyone to
know what could be wrong.

Otherwise we can only guess.

Show quote

"certolnut" wrote:

> Hi All
>
> I have a code block which works and looks like this...
>
> SELECT      i.ItemNumber
>   , i.ItemDescription
>   ,h.[Year]
>   ,d.OnHandQuantity
>   ,h.IssuedQuantity1 + h.ShippedQuantity1 as January
> FROM         dbo.FS_Item i INNER JOIN
>                       dbo.FS_ItemData d ON i.ItemKey = d .ItemDataKey INNER
> JOIN
>                       dbo.FS_InventoryHistoryYear h ON i.ItemKey = h.ItemKey
> WHERE h.[Year] >= Year(@BeginDate)
>
> However when I add a liitle line of logic to my WHERE statement, the query
> blows up...
>
>
> SELECT      i.ItemNumber
>   , i.ItemDescription
>   ,h.[Year]
>   ,d.OnHandQuantity
>   ,h.IssuedQuantity1 + h.ShippedQuantity1 as January
> FROM         dbo.FS_Item i INNER JOIN
>                       dbo.FS_ItemData d ON i.ItemKey = d .ItemDataKey INNER
> JOIN
>                       dbo.FS_InventoryHistoryYear h ON i.ItemKey = h.ItemKey
> WHERE h.[Year] >= Year(@BeginDate)
> AND
> h.[Year] <= Year(@EndDate)
>
>
> The error message I get is:
>
> Server: Msg 245, Level 16, State 1, Procedure SlowMovingInventorySP, Line 16
> [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
> varchar value 'CF07' to a column of data type int.
>
> I've tried several different iterations, converted @EndDate to a datetime
> value, used the BETWEEN statement and I still get the same error..
>
> This looks like a bug.. Anyone have any suggestions???
>
>
>

30 Jun 2005 4:02 PM

certolnut

Hi Ray, thanks so much for the reply..

The Year type in this table is Int (as in 2005) and these are the datatypes
for the parameters.

@BeginDate VarChar(10),
@EndDate VarChar(10)

The query didn't have any problem handling the conversion to datetime
without an explicit CONVERT statement. Also, I added and explicit CONVERT
statement and I got the same error...

I just found it strange that adding "AND h.[Year] <= Year(@EndDate)"
to this "WHERE h.[Year] >= Year(@BeginDate)" Throws out an ODBC error, where
the original where statement worked just fine. They use exactly the same
syntax...

It looks like a bug to me.. I also tried the

select *
From dbo.FS_InventoryHistoryYear h
where [Year] = 'CF07'

With no records returned..

Help!!!!
Show quote

"Ray" <R**@discussions.microsoft.com> wrote in message
news:49FD7C77-707C-470B-8D6A-4A91DA66F9C2@microsoft.com...
> What is the datatype for the Year field in this table?
> dbo.FS_InventoryHistoryYear h
> I don't think its a datetime value, and I'll bet query
> select *
> From dbo.FS_InventoryHistoryYear h
> where [Year] = 'CF07'
> Returns a result. But I don't know because you need to post the table
> schema, parameter declarations, and how you are setting them for anyone to
> know what could be wrong.
>
> Otherwise we can only guess.
>
> "certolnut" wrote:
>
> > Hi All
> >
> > I have a code block which works and looks like this...
> >
> > SELECT      i.ItemNumber
> >   , i.ItemDescription
> >   ,h.[Year]
> >   ,d.OnHandQuantity
> >   ,h.IssuedQuantity1 + h.ShippedQuantity1 as January
> > FROM         dbo.FS_Item i INNER JOIN
> >                       dbo.FS_ItemData d ON i.ItemKey = d .ItemDataKey
INNER
> > JOIN
> >                       dbo.FS_InventoryHistoryYear h ON i.ItemKey =
h.ItemKey
> > WHERE h.[Year] >= Year(@BeginDate)
> >
> > However when I add a liitle line of logic to my WHERE statement, the
query
> > blows up...
> >
> >
> > SELECT      i.ItemNumber
> >   , i.ItemDescription
> >   ,h.[Year]
> >   ,d.OnHandQuantity
> >   ,h.IssuedQuantity1 + h.ShippedQuantity1 as January
> > FROM         dbo.FS_Item i INNER JOIN
> >                       dbo.FS_ItemData d ON i.ItemKey = d .ItemDataKey
INNER
> > JOIN
> >                       dbo.FS_InventoryHistoryYear h ON i.ItemKey =
h.ItemKey
> > WHERE h.[Year] >= Year(@BeginDate)
> > AND
> > h.[Year] <= Year(@EndDate)
> >
> >
> > The error message I get is:
> >
> > Server: Msg 245, Level 16, State 1, Procedure SlowMovingInventorySP,
Line 16
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
the
> > varchar value 'CF07' to a column of data type int.
> >
> > I've tried several different iterations, converted @EndDate to a
datetime
> > value, used the BETWEEN statement and I still get the same error..
> >
> > This looks like a bug.. Anyone have any suggestions???
> >
> >
> >

30 Jun 2005 4:19 PM

Ray

Maybe post the entire SlowMovingInventorySP.
It is referencing a line error. you may just have a bug or type-o somewhere
else.

I have no more guesses

Show quote

"certolnut" wrote:

> Hi Ray, thanks so much for the reply..
>
> The Year type in this table is Int (as in 2005) and these are the datatypes
> for the parameters.
>
> @BeginDate VarChar(10),
> @EndDate VarChar(10)
>
> The query didn't have any problem handling the conversion to datetime
> without an explicit CONVERT statement. Also, I added and explicit CONVERT
> statement and I got the same error...
>
> I just found it strange that adding "AND h.[Year] <= Year(@EndDate)"
> to this "WHERE h.[Year] >= Year(@BeginDate)" Throws out an ODBC error, where
> the original where statement worked just fine. They use exactly the same
> syntax...
>
> It looks like a bug to me.. I also tried the
>
> select *
> From dbo.FS_InventoryHistoryYear h
> where [Year] = 'CF07'
>
> With no records returned..
>
> Help!!!!
> "Ray" <R**@discussions.microsoft.com> wrote in message
> news:49FD7C77-707C-470B-8D6A-4A91DA66F9C2@microsoft.com...
> > What is the datatype for the Year field in this table?
> > dbo.FS_InventoryHistoryYear h
> > I don't think its a datetime value, and I'll bet query
> > select *
> > From dbo.FS_InventoryHistoryYear h
> > where [Year] = 'CF07'
> > Returns a result. But I don't know because you need to post the table
> > schema, parameter declarations, and how you are setting them for anyone to
> > know what could be wrong.
> >
> > Otherwise we can only guess.
> >
> > "certolnut" wrote:
> >
> > > Hi All
> > >
> > > I have a code block which works and looks like this...
> > >
> > > SELECT      i.ItemNumber
> > >   , i.ItemDescription
> > >   ,h.[Year]
> > >   ,d.OnHandQuantity
> > >   ,h.IssuedQuantity1 + h.ShippedQuantity1 as January
> > > FROM         dbo.FS_Item i INNER JOIN
> > >                       dbo.FS_ItemData d ON i.ItemKey = d .ItemDataKey
> INNER
> > > JOIN
> > >                       dbo.FS_InventoryHistoryYear h ON i.ItemKey =
> h.ItemKey
> > > WHERE h.[Year] >= Year(@BeginDate)
> > >
> > > However when I add a liitle line of logic to my WHERE statement, the
> query
> > > blows up...
> > >
> > >
> > > SELECT      i.ItemNumber
> > >   , i.ItemDescription
> > >   ,h.[Year]
> > >   ,d.OnHandQuantity
> > >   ,h.IssuedQuantity1 + h.ShippedQuantity1 as January
> > > FROM         dbo.FS_Item i INNER JOIN
> > >                       dbo.FS_ItemData d ON i.ItemKey = d .ItemDataKey
> INNER
> > > JOIN
> > >                       dbo.FS_InventoryHistoryYear h ON i.ItemKey =
> h.ItemKey
> > > WHERE h.[Year] >= Year(@BeginDate)
> > > AND
> > > h.[Year] <= Year(@EndDate)
> > >
> > >
> > > The error message I get is:
> > >
> > > Server: Msg 245, Level 16, State 1, Procedure SlowMovingInventorySP,
> Line 16
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
> the
> > > varchar value 'CF07' to a column of data type int.
> > >
> > > I've tried several different iterations, converted @EndDate to a
> datetime
> > > value, used the BETWEEN statement and I still get the same error..
> > >
> > > This looks like a bug.. Anyone have any suggestions???
> > >
> > >
> > >
>
>
>

30 Jun 2005 4:27 PM

certolnut

Here's the whole stored procedure.. I guess I'll just have to figure out a
different way to approach this..

If you can't help, thanks for trying anyway Ray. Much appreciated....

CREATE
PROCEDURE dbo.SlowMovingInventorySP
@BeginDate VarChar(10),
@EndDate VarChar(10)
AS

SELECT      i.ItemNumber
,i.ItemDescription
,h.[Year]
,d.OnHandQuantity
,h.IssuedQuantity1 + h.ShippedQuantity1 as January
FROM         dbo.FS_Item i INNER JOIN
                      dbo.FS_ItemData d ON i.ItemKey = d .ItemDataKey INNER
JOIN
                      dbo.FS_InventoryHistoryYear h ON i.ItemKey = h.ItemKey
WHERE h.[Year] >= Year(@BeginDate) AND h.[Year] <= Year(@EndDate)

GO

Show quote

"Ray" <R**@discussions.microsoft.com> wrote in message
news:E4F5DB02-7C29-4261-87B4-CBCE361A3C1D@microsoft.com...
> Maybe post the entire SlowMovingInventorySP.
> It is referencing a line error. you may just have a bug or type-o
somewhere
> else.
>
> I have no more guesses
>
> "certolnut" wrote:
>
> > Hi Ray, thanks so much for the reply..
> >
> > The Year type in this table is Int (as in 2005) and these are the
datatypes
> > for the parameters.
> >
> > @BeginDate VarChar(10),
> > @EndDate VarChar(10)
> >
> > The query didn't have any problem handling the conversion to datetime
> > without an explicit CONVERT statement. Also, I added and explicit
CONVERT
> > statement and I got the same error...
> >
> > I just found it strange that adding "AND h.[Year] <= Year(@EndDate)"
> > to this "WHERE h.[Year] >= Year(@BeginDate)" Throws out an ODBC error,
where
> > the original where statement worked just fine. They use exactly the same
> > syntax...
> >
> > It looks like a bug to me.. I also tried the
> >
> > select *
> > From dbo.FS_InventoryHistoryYear h
> > where [Year] = 'CF07'
> >
> > With no records returned..
> >
> > Help!!!!
> > "Ray" <R**@discussions.microsoft.com> wrote in message
> > news:49FD7C77-707C-470B-8D6A-4A91DA66F9C2@microsoft.com...
> > > What is the datatype for the Year field in this table?
> > > dbo.FS_InventoryHistoryYear h
> > > I don't think its a datetime value, and I'll bet query
> > > select *
> > > From dbo.FS_InventoryHistoryYear h
> > > where [Year] = 'CF07'
> > > Returns a result. But I don't know because you need to post the table
> > > schema, parameter declarations, and how you are setting them for
anyone to
> > > know what could be wrong.
> > >
> > > Otherwise we can only guess.
> > >
> > > "certolnut" wrote:
> > >
> > > > Hi All
> > > >
> > > > I have a code block which works and looks like this...
> > > >
> > > > SELECT      i.ItemNumber
> > > >   , i.ItemDescription
> > > >   ,h.[Year]
> > > >   ,d.OnHandQuantity
> > > >   ,h.IssuedQuantity1 + h.ShippedQuantity1 as January
> > > > FROM         dbo.FS_Item i INNER JOIN
> > > >                       dbo.FS_ItemData d ON i.ItemKey = d
..ItemDataKey
> > INNER
> > > > JOIN
> > > >                       dbo.FS_InventoryHistoryYear h ON i.ItemKey =
> > h.ItemKey
> > > > WHERE h.[Year] >= Year(@BeginDate)
> > > >
> > > > However when I add a liitle line of logic to my WHERE statement, the
> > query
> > > > blows up...
> > > >
> > > >
> > > > SELECT      i.ItemNumber
> > > >   , i.ItemDescription
> > > >   ,h.[Year]
> > > >   ,d.OnHandQuantity
> > > >   ,h.IssuedQuantity1 + h.ShippedQuantity1 as January
> > > > FROM         dbo.FS_Item i INNER JOIN
> > > >                       dbo.FS_ItemData d ON i.ItemKey = d
..ItemDataKey
> > INNER
> > > > JOIN
> > > >                       dbo.FS_InventoryHistoryYear h ON i.ItemKey =
> > h.ItemKey
> > > > WHERE h.[Year] >= Year(@BeginDate)
> > > > AND
> > > > h.[Year] <= Year(@EndDate)
> > > >
> > > >
> > > > The error message I get is:
> > > >
> > > > Server: Msg 245, Level 16, State 1, Procedure SlowMovingInventorySP,
> > Line 16
> > > > [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error
converting
> > the
> > > > varchar value 'CF07' to a column of data type int.
> > > >
> > > > I've tried several different iterations, converted @EndDate to a
> > datetime
> > > > value, used the BETWEEN statement and I still get the same error..
> > > >
> > > > This looks like a bug.. Anyone have any suggestions???
> > > >
> > > >
> > > >
> >
> >
> >