-- Only add the new column if it doesn't exist.
IF NOT EXISTS (
SELECT Object_ID FROM sys.columns
WHERE Name = N'DateTimeStamp' and Object_ID = Object_ID(N'dbo.tblLog')
)
BEGIN
-- Add a new column to store a proper datetime value.
ALTER TABLE dbo.tblLog
ADD DateTimeStamp DATETIME
-- Get the char dates for conversion into proper datetime objects.
DECLARE DateList CURSOR FOR
SELECT date, hour FROM dbo.tblLog FOR UPDATE OF DateTimeStamp
-- Loop variables
DECLARE @Date CHAR(20)
DECLARE @Hour INT
-- Loop thorugh and update the new column with the current row date string as a proper datetime.
OPEN DateList
FETCH NEXT FROM DateList
INTO @Date, @Hour
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE dbo.tblLog
SET DateTimeStamp = CONVERT(DATETIME, SUBSTRING(@Date, 7, 4) +
'-' + SUBSTRING(@Date, 4, 2) + '-' + SUBSTRING(@Date, 0, 3) +
' ' + CONVERT (CHAR(2), @Hour) + ':00:00')
WHERE CURRENT OF DateList
FETCH NEXT FROM DateList INTO @Date, @Hour
END
-- Clean up.
CLOSE DateList
DEALLOCATE DateList
END
SELECT * FROM dbo.tblLog
