Geek Answers Handbook

Fill the table with two datetime columns with random dates

I have a T1 table with two datetime columns ( StartDate , EndDate ), which I have to fill with random dates under one condition:

  • EndDate value must be greater than StartDate for at least one day.

Example:

 StartDate EndDate =========================== 2001-04-04 2001-04-06 (2 days) 2001-01-05 2001-01-15 (10 days) . . .

Can I do this in one statement?

PS My first idea was to change the EndDate column to NULL and fill in the StartDate in the first stage, leaving EndDate as NULL, and in the second - write some mechanism for updating EndDate with dates longer (in different number of days for each record), then StartDate

+4
source share
3 answers

Here's a solution that populates the table in one step:

 insert into T1 (StartDate, EndDate) select X.StartDate, dateadd(day, abs(checksum(newid())) % 10, X.StartDate) EndDate from ( select top 20 dateadd(day, -abs(checksum(newid())) % 100, convert(date, getDate())) StartDate from sys.columns c1, sys.columns c2 ) X

The above query uses some tricks, which I often often use in special SQL queries:

  • new_Id() generates different random values ​​for each row, unlike RAND() , which will be evaluated once per request. The expression abs(checksum(newid())) % N generates random integer values ​​in the range 0 - N-1 .
  • the TOP X ... FROM sys.columns c1, sys.columns c2 allows you to generate X rows whose values ​​can be composed of scalar values, such as those shown in our example.

Obviously, you can change the hardcoded values ​​in the above query to:

  • generate more lines
  • increase the range of random start dates
  • increase the maximum duration of each line.
+2
source

Something simple with the rand() function:

Script example

 declare @records int = 100, --Number of records needed @count int = 0, @start int, @end int while(@records>@count) begin select @start = rand() * 10, @end = rand() * 100, @count+=1 insert into mytable select dateadd(day, @start, getdate()),dateadd(day, @end, getdate()) end select * from mytable
+1
source 
 INSERT T1 (StartDate, EndDate) select T1, T1 + add_days from (select DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0) T1, ROW_NUMBER() OVER(ORDER BY number) add_days from [ master ] .. spt_values) X;

sqlfiddle example

+1
source

More articles:


All Articles