CODEWITHSUNDEEP
sqlsql-serversql-server-2008
DrNice
DrNice

Reputation: 51

sql server casting some data

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

Upvotes: 0

Views: 88

Answers (3)

DrNice
DrNice

Reputation: 51

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

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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

Upvotes: 0

gbn
gbn

Reputation: 432261

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'

Upvotes: 2

Related Questions