samedi 9 mai 2015

SQL Server Cursor Converting INT to Date

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

Aucun commentaire:

Enregistrer un commentaire