Method 1
select DATEADD(DAY, -(ABS(CHECKSUM(NEWID()) % 36500 )), getdate());
Sample outputs:
1980-11-10 02:19:37.643
1940-08-25 02:20:06.217
1967-10-10 02:20:15.030
2013-03-20 02:20:24.933
1951-11-19 02:20:38.973
To summarize, the following code generates a random number between 0 and 36500. (36500 days roughly equals to 100 years; you can use 36525 to make it exactly 100 years.)
ABS(CHECKSUM(NEWID()) % 36500 )
By reducing the present day by that randomly generated number (random number of days), you will be able to get a random date for a person between the ages of 0 and 100.
Demo: http://sqlfiddle.com/#!18/9eecb/15528/0
Method 2
DECLARE @start DATE = '1980-01-01'
DECLARE @end DATE = '1980-01-05'
SELECT DATEADD(DAY,ABS(CHECKSUM(NEWID())) % DATEDIFF(DAY,@start,@end) ,@start)
Sample outputs:
1980-01-01
1980-01-04
1980-01-03
1980-01-02
Using the DATEDIFF
function, you get get the difference between two dates. In this case (DATEDIFF(DAY,@start,@end
), the difference between the start date and end date will be obtained in days. By adding this value to start date, you can generate random dates between the start date and end date.
However, this will not return the end date (1980-01-05
) as a randomly generated date. To get that, you can add 1 to the difference.
SELECT DATEADD(DAY,ABS(CHECKSUM(NEWID())) % ( 1 + DATEDIFF(DAY,@start,@end)),@start)
Demo: http://sqlfiddle.com/#!18/9eecb/15542/0
Method 3
Example 1
SELECT DATEADD(DAY, RAND() * ((-36500) - 1), GETDATE())
Sample Output:
1956-02-25T23:44:17.62Z
2006-09-08T23:44:40.62Z
1987-06-13T23:44:53.717Z
Example 2
SELECT DATEADD(DAY, RAND() * ((-1) - 1), GETDATE())
Sample Output:
2018-05-03 06:32:56.753
2018-05-02 06:32:56.753
Note: If you remove the '- 1
', 2018-05-02 06:32:56.753
will not be generated.
Demo: http://sqlfiddle.com/#!18/9eecb/15554/0
Method 4
DECLARE @start DATE = '1980-01-01'
DECLARE @end DATE = '1980-01-05'
SELECT DATEADD(DAY, RAND() * DATEDIFF(DAY,@start,@end) ,@start)
Sample outputs:
1980-01-02
1980-01-01
1980-01-03
1980-01-04
Note: This will not also return the end date (1980-01-05
) as a randomly generated date. You get that you have to add 1 like this.
DATEDIFF(DAY,@start,@end) +1
Demo: http://sqlfiddle.com/#!18/9eecb/15538/0
Method 5
DECLARE @from INT = 18
DECLARE @to INT = 70
DECLARE @tfrom DATE = DATEADD(YEAR, -(@from), GETDATE())
DECLARE @tto DATE = DATEADD(YEAR, -(@to), GETDATE())
DECLARE @diff INT = DATEDIFF(DAY, @tfrom, @tto)
SELECT DATEADD(DAY, RAND() * (-(@diff) - 1), @tto)
Sample outputs:
1967-11-03
1955-10-09
1967-06-03
1962-11-17
1970-07-04
Demo: http://sqlfiddle.com/#!18/9eecb/15555/0