IsNumeric??

[SQLScholar]

Hey all.

This is the current where criteria i have on a query

WHERE     
 	cast(LEFT(CAST(CONVERT(varchar, DATEADD(d, 14, @RunDate), 103) AS varchar), 2) as numeric) = cast(policytables.[Collection Date] as numeric)

I am getting an Error converting data type varchar to numeric.

So.... i changed there where to

WHERE
(Isnumeric(LEFT(CAST(CONVERT(varchar, DATEADD(d, 14, @RunDate), 103) AS varchar), 2)) = 0
OR
ISNUMERIC (policytables.[Collection Date])=0)

And i get through... none!

If i removing the numeric casting off the first where it runs ok - just isnt correct.

So why if there is no non numerics - cant i cast as numeric?

Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[SQLSister]

Show us some sample data and what are the actual data types of your fields originally?

Questions about posting. See faq183-874

 

[AlexCuse]

You would be better served using one of SQL Server's date functions to get your month (looks like thats' what your trying to do)

Using something like Month(@RunDate) or Day(@RunDate) (not sure of your location) will get you what you are after without running around so much.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Also, it needs to = Month(CollectionDate) or Day(CollectionDate)

(provided they are date columns of course)

Ignorance of certain subjects is a great part of wisdom

 

[SQLScholar]

SQLsister,

Dont know any easy way to show you data - as the code has about 20/30 fields. In terms of datatypes - the @rundate is a datetime. The collection date is a varchar - and just stores the first two charactors of what day each month to collect monies. Should have been called collection day really!

Alex,

I have changed my code to

cast(day(DATEADD(d, 14, @RunDate)) as numeric) = cast(policytables.[Collection Date] as numeric)

Collection date is fine (see comment above to SQLsister).

Code is cleaner (dont know why i did it like that?) but still same problem

Dan


----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[gmmastros]

You must have bad data. Try running this to find it.

Select *
From   Policytables
Where  IsNumeric([Collection Date]) = 0

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

You say:

collection date is a varchar - and just stores the first two charactors of what day

This is why SQLSister asked for samples of the data...are collection date values: 01, 05 or are they Mo, Fr?

-SQLBill

Posting advice: FAQ481-4875