Note: This will not also return the end date ( ) as a randomly generated date. Note: If you remove the ' - 1', 06:32:56.753 will not be generated. To get that, you can add 1 to the difference. However, this will not return the end date ( ) as a randomly generated date. By adding this value to start date, you can generate random dates between the start date and end date. In this case ( the difference between the start date and end date will be obtained in days. Using the DATEDIFF function, you get get the difference between two dates. SELECT DATEADD(DAY,ABS(CHECKSUM(NEWID())) % outputs: (36500 days roughly equals to 100 years you can use 36525 to make it exactly 100 years.) ABS(CHECKSUM(NEWID()) % 36500 )īy 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.
#Random sql data generator code#
To summarize, the following code generates a random number between 0. Method 1 select DATEADD(DAY, -(ABS(CHECKSUM(NEWID()) % 36500 )), getdate())
, CONVERT(date, DATEADD(YEAR, 1 - GETDATE())) The following example will randomly choose birth dates resulting in ages between 10 and 20 years old: DECLARE int SET = DATEADD(DAY, (1 - CONVERT(int, CRYPT_GEN_RANDOM(2)) / 3), GETDATE())
#Random sql data generator update#
One could reverse the logic such that you have people of various ages from 0 days old to approximately 59 years old with this: UPDATE dbo.MyTable įor a table named dbo.MyTable, with a column named, this will update all column values to randomly generated dates: UPDATE dbo.MyTable So CRYPT_GEN_RANDOM(2) returns a two-byte number in the range of 0x0000 to 0xFFFF, when converted into a signed-integer and "added" to, will result in dates in the range of to. The output is a hexadecimal number of the specified number of bytes." This will add a random number of days to 1st of January, 1900: SELECT DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(2)), 'T00:00:00') Īccording to the Microsoft Docs, CRYPT_GEN_RANDOM "returns a cryptographic random number generated by the Crypto API (CAPI).
0 kommentar(er)
