2

I want to insert a specific start date for my products. It should be between 48 hours ago and now.

This is my code: 

CREATE VIEW getNewID AS 
         SELECT         
         DATEADD(MILLISECOND, (ROUND((999 * NEWID()), 0)), 
         DATEADD(SECOND, (ROUND((((DATEDIFF(SECOND, (DATEADD(DAY, -2, GETDATE())),
         (GETDATE())))-1) * NEWID()), 0)), (DATEADD(DAY, -2, GETDATE())))) 
         AS new_id

This view will return a random date between the ranges specified. The function below will return the NEWID() because SQL won't let me use NEWID()'s in a function.

However, the view above will give me an error :

the uniqueidentifier is incompatible with int.

This won't happen if I use RAND(), but this will give me the same value for all the rows I insert (I want to use this for my conversion script).

CREATE FUNCTION randomID()
RETURNS uniqueidentifier
AS BEGIN
   RETURN (SELECT new_id FROM getNewID)
END

Maybe some of you have encountered this problem?

DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
Thomas Brandhorst
  • 39
  • 6

1 Answers1

0

You could generate a number between 0 and 48*60*60*1000 (48 hours ago) like this:

abs(checksum(newid())) % (48*60*60*1000)

And use DATEADD

SELECT dateadd(millisecond, 0 - abs(checksum(newid())) % (48*60*60*1000), getdate())
TriV
  • 5,118
  • 2
  • 10
  • 18