技术请求：生成指定范围随机日期并更新myTable的dateStart与dateEnd字段

Got it, let's tackle this problem step by step. You need to update your myTable to set random dates for dateStart and dateEnd—with dateStart falling between '2014-01-01' and '2018-01-01', and dateEnd always being on or after the row's new dateStart (up to '2018-01-01').

Since the way to generate random dates varies across database systems, I'll cover solutions for the most popular ones:

MySQL Solution #

You can update both fields in a single statement to ensure dateEnd uses the fresh dateStart value for each row. We'll calculate the number of days in the target range, multiply by a random decimal, then add that offset to the starting date:

UPDATE myTable
SET 
    dateStart = DATE_ADD('2014-01-01', INTERVAL FLOOR(RAND() * DATEDIFF('2018-01-01', '2014-01-01')) DAY),
    dateEnd = DATE_ADD(dateStart, INTERVAL FLOOR(RAND() * DATEDIFF('2018-01-01', dateStart)) DAY)
WHERE 1=1; -- Add a WHERE clause if you don't want to update every row

Quick breakdown: #

• DATEDIFF('2018-01-01', '2014-01-01') calculates total days between the two dates (1461, including the 2016 leap year)
• RAND() * [day count] generates a random decimal between 0 and the total days
• FLOOR() converts that decimal to an integer number of days to add
• DATE_ADD() applies the random offset to the base date

PostgreSQL Solution #

PostgreSQL uses random() for decimal random values, and we can work with date intervals directly. Here's a concise single-update approach:

UPDATE myTable
SET 
    dateStart = '2014-01-01'::DATE + (random() * ('2018-01-01'::DATE - '2014-01-01'::DATE))::INTEGER * INTERVAL '1 day',
    dateEnd = dateStart + (random() * ('2018-01-01'::DATE - dateStart))::INTEGER * INTERVAL '1 day'
WHERE true; -- Adjust the WHERE clause if targeting specific rows

If you prefer a more explicit two-step approach using a CTE:

WITH updated_start AS (
    UPDATE myTable
    SET dateStart = '2014-01-01'::DATE + floor(random() * (('2018-01-01'::DATE - '2014-01-01'::DATE) + 1))::INTEGER
    RETURNING *
)
UPDATE myTable t
SET dateEnd = t.dateStart + floor(random() * (('2018-01-01'::DATE - t.dateStart) + 1))::INTEGER
FROM updated_start us
WHERE t.id = us.id; -- Replace `id` with your table's primary key

SQL Server Solution #

SQL Server uses DATEADD and DATEDIFF, but note that RAND() returns the same value for all rows in one update. To get unique random values per row, we'll use NEWID() to seed the randomness:

UPDATE myTable
SET 
    dateStart = DATEADD(DAY, ABS(CHECKSUM(NEWID())) % DATEDIFF(DAY, '2014-01-01', '2018-01-01'), '2014-01-01'),
    dateEnd = DATEADD(DAY, ABS(CHECKSUM(NEWID())) % (DATEDIFF(DAY, dateStart, '2018-01-01') + 1), dateStart)
WHERE 1=1; -- Add a WHERE condition if updating specific rows only

Quick breakdown: #

• CHECKSUM(NEWID()) generates a random positive/negative integer
• ABS() converts it to a positive number
• % [day count] gives a random number between 0 and (day count - 1)
• DATEADD() applies the random day offset to the base date

Pro tip: Always test these statements on a copy of your data first to confirm the results match your expectations!

内容的提问来源于stack exchange，提问作者peterHasemann