4 added link to another answer as quoted
source | link

@Doug-Deden@Doug-Deden has the right starting point, but I just wanted to try to answer what I thought was the original intention of the question - how to apply it to a result set with increasing milliseconds per row.

In that case, you can use ROW_NUMBER and a Common Table Expression (edit as needed for you table structure, including joins, etc.).

Select to show values:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
SELECT TOP 1000 *, DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2)) [new_date]
FROM CTE
ORDER BY my_date_column

Update joins back to original table:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
UPDATE t SET 
my_date_column = DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2))
FROM CTE c
     JOIN Table1 t ON c.my_id = t.my_id

@Doug-Deden has the right starting point, but I just wanted to try to answer what I thought was the original intention of the question - how to apply it to a result set with increasing milliseconds per row.

In that case, you can use ROW_NUMBER and a Common Table Expression (edit as needed for you table structure, including joins, etc.).

Select to show values:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
SELECT TOP 1000 *, DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2)) [new_date]
FROM CTE
ORDER BY my_date_column

Update joins back to original table:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
UPDATE t SET 
my_date_column = DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2))
FROM CTE c
     JOIN Table1 t ON c.my_id = t.my_id

@Doug-Deden has the right starting point, but I just wanted to try to answer what I thought was the original intention of the question - how to apply it to a result set with increasing milliseconds per row.

In that case, you can use ROW_NUMBER and a Common Table Expression (edit as needed for you table structure, including joins, etc.).

Select to show values:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
SELECT TOP 1000 *, DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2)) [new_date]
FROM CTE
ORDER BY my_date_column

Update joins back to original table:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
UPDATE t SET 
my_date_column = DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2))
FROM CTE c
     JOIN Table1 t ON c.my_id = t.my_id
3 Removed unnecessary alias in update statement
source | link

@Doug-Deden has the right starting point, but I just wanted to try to answer what I thought was the original intention of the question - how to apply it to a result set with increasing milliseconds per row.

In that case, you can use ROW_NUMBER and a Common Table Expression (edit as needed for you table structure, including joins, etc.).

Select to show values:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
SELECT TOP 1000 *, DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2)) [new_date]
FROM CTE
ORDER BY my_date_column

Update joins back to original table:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
UPDATE t SET 
my_date_column = DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2)) [new_date]
FROM CTE c
     JOIN Table1 t ON c.my_id = t.my_id

@Doug-Deden has the right starting point, but I just wanted to try to answer what I thought was the original intention of the question - how to apply it to a result set with increasing milliseconds per row.

In that case, you can use ROW_NUMBER and a Common Table Expression (edit as needed for you table structure, including joins, etc.).

Select to show values:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
SELECT TOP 1000 *, DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2)) [new_date]
FROM CTE
ORDER BY my_date_column

Update joins back to original table:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
UPDATE t SET 
my_date_column = DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2)) [new_date]
FROM CTE c
     JOIN Table1 t ON c.my_id = t.my_id

@Doug-Deden has the right starting point, but I just wanted to try to answer what I thought was the original intention of the question - how to apply it to a result set with increasing milliseconds per row.

In that case, you can use ROW_NUMBER and a Common Table Expression (edit as needed for you table structure, including joins, etc.).

Select to show values:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
SELECT TOP 1000 *, DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2)) [new_date]
FROM CTE
ORDER BY my_date_column

Update joins back to original table:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
UPDATE t SET 
my_date_column = DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2))
FROM CTE c
     JOIN Table1 t ON c.my_id = t.my_id
2 Changed ms to MILLISECOND to avoid ambiguity.
source | link

@Doug-Deden has the right starting point, but I just wanted to try to answer what I thought was the original intention of the question - how to apply it to a result set with increasing milliseconds per row.

In that case, you can use ROW_NUMBER and a Common Table Expression (edit as needed for you table structure, including joins, etc.).

Select to show values:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
SELECT TOP 1000 *, DATEADD(msMILLISECOND, R, CAST(my_date_column AS datetime2)) [new_date]
FROM CTE
ORDER BY my_date_column

Update joins back to original table:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
UPDATE t SET 
my_date_column = DATEADD(msMILLISECOND, R, CAST(my_date_column AS datetime2)) [new_date]
FROM CTE c
     JOIN Table1 t ON c.my_id = t.my_id

@Doug-Deden has the right starting point, but I just wanted to try to answer what I thought was the original intention of the question - how to apply it to a result set with increasing milliseconds per row.

In that case, you can use ROW_NUMBER and a Common Table Expression (edit as needed for you table structure, including joins, etc.).

Select to show values:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
SELECT TOP 1000 *, DATEADD(ms, R, CAST(my_date_column AS datetime2)) [new_date]
FROM CTE
ORDER BY my_date_column

Update joins back to original table:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
UPDATE t SET 
my_date_column = DATEADD(ms, R, CAST(my_date_column AS datetime2)) [new_date]
FROM CTE c
     JOIN Table1 t ON c.my_id = t.my_id

@Doug-Deden has the right starting point, but I just wanted to try to answer what I thought was the original intention of the question - how to apply it to a result set with increasing milliseconds per row.

In that case, you can use ROW_NUMBER and a Common Table Expression (edit as needed for you table structure, including joins, etc.).

Select to show values:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
SELECT TOP 1000 *, DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2)) [new_date]
FROM CTE
ORDER BY my_date_column

Update joins back to original table:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
UPDATE t SET 
my_date_column = DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2)) [new_date]
FROM CTE c
     JOIN Table1 t ON c.my_id = t.my_id
1
source | link