SQL date conversion for 8 char value with 00 as days

Question

I have been having trouble trying to figure out how to properly convert the data. In its current form it is varchar and the data is as follows:

19260200, 19770900, 20030400, 20120300, 20020500,

So as you can see I have the year and months, but have no values for dates. When I try to insert it into SQL field formatted as datetime i get errors. Anyone know how i can fix this? I am working with a DB of 700,000 records so I need to be able to write some code to address it and not just manually go through and change.

Thanks in advance for the help!!!

Well what do you want to insert, the first of every month when the days are '00'? — Aaron Bertrand, Jul 17 '12 at 02:01

Aaron Bertrand · Accepted Answer · 2012-07-17T16:32:41.300

1

INSERT dbo.destination(date_column)
SELECT dt FROM 
(
  SELECT dt = CASE 
    WHEN src_date_column LIKE '%00' THEN 
      STUFF(src_date_column, 8, 1, '1') 
    ELSE src_date_column 
  END
  FROM dbo.source
) AS x
WHERE ISDATE(dt) = 1;

To address comments:

AS x is just an alias. A derived table needs to be called something. If you just say:

SELECT x FROM (SELECT x = 1);

You get this unhelpful error:

Msg 102, Level 15, State 1
Incorrect syntax near ';'.

But not if you say:

SELECT x FROM (SELECT x = 1) AS y;

As for your other question, you can simply add other columns to the inner SELECT, e.g.:

INSERT dbo.Citation(PatentNo, Citation, CitedBy, CitationDate)
SELECT PatentNo, Citation, WhoCitedThis, dt 
FROM 
(
  SELECT PatentNo, CitationSource, WhoCitedThis, dt = CASE
    WHEN CitationDate LIKE '%00' THEN STUFF(CitationDate, 8, 1, '1') 
    ELSE CitationDate 
  END 
  FROM dbo.CitationSource
) AS x
WHERE ISDATE(dt) = 1;

Now, you need to decide what you want to do with invalid dates, such as 20120231 or 20120399. Those are just left behind. My first recommendation would be to make that a proper date or datetime column, and reject bad dates from getting into the system in the first place.

edited Jul 17 '12 at 16:32

answered Jul 17 '12 at 02:03

Aaron Bertrand

272,866
37
466
490

Worked great! What does the "AS x" refer to. I am still fairly new to SQL and would like to learn as much as possible – Johnny B Jul 17 '12 at 05:15
the other question I have is how do I put that into the existing insert code which is: 'insert into Citation (PatentNo, Citation, CitedBy) select PatentNo, Citation, whocitedthis from citationsource' The modified version of above that worked in a test DB is: 'INSERT citation(citationdate) SELECT dt FROM ( SELECT dt = CASE WHEN CitationDate LIKE '%00' THEN STUFF(CitationDate, 8, 1, '1') ELSE CitationDate END FROM CitationSource ) AS x WHERE ISDATE(dt) = 1;' – Johnny B Jul 17 '12 at 05:30
That makes much more sense to me, thank you @Aaron Bertrand. The problem isn't invalid dates it is just that the dates on the xml file that populates the flat file that is then brought into SQL adds the 00 for the date cause it is blank. I figured it would be easier to correct this in SQL then the python program that someone else wrote ages ago. – Johnny B Jul 17 '12 at 18:45

score 0 · Answer 2 · answered Jul 17 '12 at 02:01

0

You can try something like this:

cast(left(val, 6)+'01') as date)

That is, insert a valid day value into the string and do the conversion.

answered Jul 17 '12 at 02:01

Gordon Linoff

1,242,037
58
646
786

SQL date conversion for 8 char value with 00 as days

2 Answers2