0

I have a column where the date is stored as an integer. This is something that we cannot change because it is a part of an older ERP system. If I could change the column type to date or datetime I would.

The dates are stored as yymmdd

I am trying to run a cursor to update off of this column to only grab rows where the date equals yesterday's date. My cursor below. Any advice?

DECLARE @ID NVARCHAR(50)
DECLARE @D1 NVARCHAR(50)
DECLARE @D2 NVARCHAR(50)
DECLARE CUR CURSOR FOR
    SELECT IMITNO, IMITD1, IMITD2 FROM [AS400].S062F7AR.APLUS83FDS.ITMST
        WHERE ISDATE(CONVERT(CHAR(6), IMLMDT)) >= DATEADD(DAY,-1,GETDATE());

OPEN CUR
FETCH NEXT FROM cur INTO @ID, @D1, @D2
WHILE (@@FETCH_STATUS = 0)
BEGIN

    UPDATE ProductBase
        SET new_AS400Description = @D1 + ' ' + @D2 
                WHERE ProductNumber = @ID

FETCH NEXT FROM CUR INTO @ID, @D1, @D2
END
CLOSE CUR
DEALLOCATE CUR
Matt Weick
  • 332
  • 6
  • 19

2 Answers2

1

Give this one a try:

replace your select with this one:

SELECT * FROM 
( 
    SELECT IMITNO, IMITD1, IMITD2, IMLMDT FROM [AS400].S062F7AR.APLUS83FDS.ITMST 
        WHERE 
            ISDATE( 
                '2'+RIGHT('0000'+CAST(IMLMDT AS VARCHAR), 3) + '-' + 
                SUBSTRING(RIGHT('0'+CAST(IMLMDT AS VARCHAR), 6), 3, 2) + '-' + 
                SUBSTRING(RIGHT('0'+CAST(IMLMDT AS VARCHAR), 6), 5, 2) 
            ) = 1 
    ) AS A 
WHERE 
    IMLMDT >= CAST(CONVERT(Varchar, DATEADD(DAY,-1,GETDATE()), 12) AS INT);

Since you are probably having some incorrect values on that column, the above SQL is first testing for valid date formats and only working with that ones.

Marcelo Myara
  • 2,841
  • 2
  • 27
  • 36
  • I ran your statement and still got the conversion error message. I double checked the data in the actual ERP table and they are all 6 digits long and none of them are equal to '000000' or '999999'. Thanks for attempting – Matt Weick May 09 '15 at 18:58
  • Ok, this is most probably because of an invalid data format (since the SQL is very straightforward and relies only on simple functions). So what I would do in your shoes would be to verify your data. You can run several SELECTs trying to find a row that stores an illegal value (for the YYMMDD format). – Marcelo Myara May 09 '15 at 19:01
  • Will do - let me see what I can find, I'll run some searches for illegal characters. This is the only thing I didn't check for. – Matt Weick May 09 '15 at 19:02
  • For instance: if you run a select SUBSTRING(RIGHT('0'+CAST(IMLMDT AS VARCHAR), 6), 5, 2) againt your table, you will be getting only the supposed DAYs portion... Try that and try find something odd (like a 0, or anithing above 31). If you use the SUBSTRING(RIGHT('0'+CAST(IMLMDT AS VARCHAR), 6), 3, 2) you would be getting the MONTHs portion (same thing, try to find something odd, like 0 or above 12). Tell me what you got, ok? – Marcelo Myara May 09 '15 at 19:03
  • `SELECT SUBSTRING(RIGHT('0'+CAST(IMLMDT AS VARCHAR), 6), 5, 2) FROM [AS400].S062F7AR.APLUS83FDS.ITMST; -- DAY portion` `SELECT SUBSTRING(RIGHT('0'+CAST(IMLMDT AS VARCHAR), 6), 3, 2) FROM [AS400].S062F7AR.APLUS83FDS.ITMST; -- MONTH portion` – Marcelo Myara May 09 '15 at 19:06
  • For Days - SQL returned some blanks (assuming it read it as '00') and it also returned some single digits of '4' '5' and '6'. Most values were between '01' and '31' – Matt Weick May 09 '15 at 19:19
  • For Months - SQL returned some blanks (assuming it read it as '00') and it also returned 26 values greater than '12'. Most values were between '01' and '12' – Matt Weick May 09 '15 at 19:20
  • Yup, that's the problem. Assuming that you may have some null values, what would you want to do with them? – Marcelo Myara May 09 '15 at 19:23
  • You should try the complete statement (on my answer) adding a clause to filter just some rows (that you now that are filled and has valid values on that column). Just to try it out and tell us that it works on correct datas... – Marcelo Myara May 09 '15 at 19:25
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/77412/discussion-between-marcelo-myara-and-matt-weick). – Marcelo Myara May 09 '15 at 19:33
0

Try changing your select statement to

SELECT IMITNO, IMITD1, IMITD2 FROM [AS400].S062F7AR.APLUS83FDS.ITMST
    WHERE CONVERT(datetime,RIGHT('000000' + convert(char(6), IMLMDT),7)) >=
    DATEADD(DAY,-1,GETDATE());
Peter Schneider
  • 2,879
  • 1
  • 14
  • 17
  • I tried that a little bit ago and got "Conversion failed when converting date and/or time from character string." – Matt Weick May 09 '15 at 16:39
  • Does IMLMDT have leading zeros? – Peter Schneider May 09 '15 at 16:44
  • Yes. for example 2009 date would be stored as 091126 but SQL reads as 91126 – Matt Weick May 09 '15 at 16:46
  • Yes and that's the problem... perhaps you can get along with division by 1000, 100 like [here](http://stackoverflow.com/questions/2781419/optimal-way-to-convert-to-date). You would then replace the IMLMDT column with the expression... – Peter Schneider May 09 '15 at 16:49
  • I was reading over the link you provided trying to figure it out and I still have no luck. I just tried your updated statement and still getting the same converting error message – Matt Weick May 09 '15 at 17:16
  • You have to take a look at the values in IMLMDT and if they are all in the format ymd. Probably there are some values, which don't match... here's a test script: `declare @i int select @i = 090215 SELECT RIGHT('000000' + convert(char(6), @i),7) SELECT CONVERT(datetime,RIGHT('000000' + convert(char(6), @i),7))` so that you can try out different int values... – Peter Schneider May 09 '15 at 17:24
  • I think I found the problem based on your test script. The problem is with the year 2000 and any year that does't being with a 0, getting error on conversion only for 2000, 2010, 2011, etc... It's not liking it when the value doesn't start with a 0 for the digit year – Matt Weick May 09 '15 at 17:44