Hi MElbakly,
Refer the below query.
DECLARE @Test AS TABLE(ID INT,TIMEDATE VARCHAR(15))
INSERT INTO @Test VALUES(7,'2008.10.08')
INSERT INTO @Test VALUES(11,'2008.10.07')
INSERT INTO @Test VALUES(14,'2008.10.06')
INSERT INTO @Test VALUES(15,'2008.10.05')
INSERT INTO @Test VALUES(22,'2008.10.04')
INSERT INTO @Test VALUES(24,'2008.10.03')
INSERT INTO @Test VALUES(25,'2008.10.02')
INSERT INTO @Test VALUES(30,'2008.10.01')
DECLARE @Count INT
SET @Count = 1
WHILE (@Count <= (SELECT COUNT(*) FROM @Test))
BEGIN
DECLARE @TimeDate VARCHAR(15), @Year VARCHAR(15), @Month VARCHAR(2), @Date VARCHAR(2), @TimeDateAlter VARCHAR(15), @ID INT
SET @TimeDate = (SELECT TIMEDATE FROM (SELECT ROW_NUMBER() OVER (ORDER BY(SELECT 0)) AS RowNo,TIMEDATE FROM @Test) a WHERE RowNo = @Count)
SET @ID = (SELECT ID FROM (SELECT ROW_NUMBER() OVER (ORDER BY(SELECT 0)) AS RowNo,ID FROM @Test) b WHERE RowNo = @Count)
SET @Year = SUBSTRING(@TimeDate,1,4) -- Year
SET @Month = SUBSTRING(@TimeDate,6,2) -- Month
SET @Date = SUBSTRING(@TimeDate,9,2) -- Date
UPDATE @Test SET TIMEDATE = @Year + '.' + @Date + '.' + @Month WHERE ID = @ID
SET @Count = @Count + 1
END
SELECT * FROM @Test
Output
| ID |
TIMEDATE |
| 7 |
2008.08.10 |
| 11 |
2008.07.10 |
| 14 |
2008.06.10 |
| 15 |
2008.05.10 |
| 22 |
2008.04.10 |
| 24 |
2008.03.10 |
| 25 |
2008.02.10 |
| 30 |
2008.01.10 |
