sql server casting some data -

i got numeric(8,0) date column, values 20130101. need cast date format , queries.

my test query looks this

select * hund  isdate(hund.hfdat) = 1   , cast((left(convert(varchar(8),hund.hfdat),4) +       substring(convert(varchar(8),hund.hfdat),5,2) + right(hund.hfdat,2))        datetime) between '20050101' , '20300101' 

i error

conversion failed when converting date and/or time character string.

i guess 'date' column bad data. suggestion write in other way?

i want jack this, dogs not older 10 years

select ras_.rasnamn 'ras', count(distinct person.personid) 'antal     ägare', count(distinct jbv_aegare.hundid) 'antal djur'   jbv_aegare inner join hund on jbv_aegare.hundid=hund.hundid inner join ras_ on hund.ras=ras_.raskod inner join person on jbv_aegare.personid=person.personid inner join postnummerlan on person.postnr=postnummerlan.pn_postnummer inner join land on postnummerlan.pn_lan=land.landkod postnr <> 0 , person.landkod=0 , here ->>> hund.hfdat >=      convert(char(8),dateadd(year, -1, getdate()),112) ,  hund.hfdat <=   (year(getdate()) + 10)  group ras_.rasnamn  order ras_.rasnamn 

it should simple double cast

declare @whyisthisnumeric decimal(8,0) = 20130101 select cast(cast(@whyisthisnumeric varchar(8)) datetime) 

when attempt cast 20130101 (not '20130101': it's number, not string) evaluated 20,130,101 days after 01 jan 1900 utterly invalid datetime value.

also, sql declarative language , optimiser not left right evaluation. isdate evaluated first conversion fails.

in case, need store dates dates.

otherwise need force isdate evaluated first forcing materialisation of isdate before cast evaluated

select *      (     select top 2000000000 *     hund      isdate(hund.hfdat) = 1     order hund.hfdat --better use pk     ) x     cast(cast(x.hfdat varchar(8)) datetime) between '20050101' , '20300101'