sql server casting some data

Question

I got a numeric(8,0) date column, with values like 20130101. I need to cast it to some date format and do some queries.

My test query looks like this

SELECT *
FROM hund 
WHERE ISDATE(hund.hfdat) = 1
  and cast((left(convert(varchar(8),hund.hfdat),4) +
      substring(convert(varchar(8),hund.hfdat),5,2) + right(hund.hfdat,2)) 
      as datetime) between '20050101' and '20300101'

I get this error

Conversion failed when converting date and/or time from character string.

I guess my 'date' column get some bad data. Some suggestion to write it in some other way?

I want to jack this into this, dogs not older than 10 years

SELECT Ras_.rasnamn as 'Ras', count(distinct person.personid) as 'Antal  
  ägare', count(distinct JBV_Aegare.hundid) as 'Antal djur'
  FROM 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
where postnr <> 0 and person.landkod=0 and HERE ->>> hund.hfdat >=  
   convert(CHAR(8),DATEADD(YEAR, -1, GETDATE()),112) and
 hund.hfdat <=   (year(getdate()) + 10)
 group by Ras_.rasnamn
 order by Ras_.rasnamn

please look into this http://stackoverflow.com/questions/15098651/convert-date-stored-as-numeric-to-datetime — Deepak Joshi, Apr 25 '16 at 07:11

score 2 · Answer 1 · answered Apr 25 '16 at 07:12

It should be a simple double cast

DECLARE @WhyIsThisNumeric decimal(8,0) = 20130101
SELECT CAST(CAST(@WhyIsThisNumeric AS varchar(8)) AS datetime)

When you attempt to cast 20130101 (not '20130101': it's a number, not a string) then it is evaluated as 20,130,101 days after 01 Jan 1900 which is an utterly invalid datetime value.

Also, SQL is a declarative language and the optimiser will not do a left to right evaluation. The ISDATE could be evaluated first thus the conversion fails.

In this case, you really need to store dates as dates.

Otherwise you need force the ISDATE to be evaluated first by forcing a materialisation of the ISDATE before the CAST is evaluated

SELECT *
FROM

    (
    SELECT TOP 2000000000 *
    FROM hund 
    WHERE ISDATE(hund.hfdat) = 1
    ORDER BY hund.hfdat --better to use the PK
    ) X
WHERE 
   CAST(CAST(X.hfdat AS varchar(8)) AS datetime) between '20050101' and '20300101'

I'd suggest you don't demand people write your code for you *after* they've already answered the original question, which is caused by a bad design. — gbn, Apr 25 '16 at 12:09

score 0 · Answer 2 · edited May 23 '17 at 12:07

Try this to find out what you should do:

DECLARE @tblTest TABLE(ndat NUMERIC(8,0));
INSERT INTO @tblTest VALUES
 ('20130101')
,('20131210')
--,('20131310') --an invalid date (month=13)
;

SELECT --CAST(ndat AS DATE), --doesn't work
       CAST(ndat AS VARCHAR(8)) AS YourNumAsString, 
       CAST(CAST(ndat AS VARCHAR(8)) AS DATE) AS YourNumAsString_via_UniversalFormat
FROM @tblTest;

Converting from and to date/time formats is always a pain in the neck. It helps a lot if you use "independant standard formats" as intermediate. You might read this: https://stackoverflow.com/a/34275965/5089204

score 0 · Accepted Answer · edited Apr 27 '16 at 23:14

0

hund.hfdat >= replace(CONVERT(date, DATEADD(year, -10, getdate())),'-','')

edited Apr 27 '16 at 23:14

Shnugo

66,100
9
53
114

answered Apr 26 '16 at 07:02

DrNice

51
6

sql server casting some data

3 Answers3