October 12, 2016 at 1:25 pm
I am trying to create a function that will generate a random datetime value. Due to being unable to use NEWID() and RAND() in UDFs, I created two SPs to generate the date part and time part.
CREATE PROC [dbo].[GenerateRandomDate]
(
@RandomDate DATETIME OUTPUT,
@MinimumDate DATETIME = NULL,
@MaximumDayDifference INT = NULL
)
AS
BEGIN
IF @MinimumDate IS NULL
SET @MinimumDate = DATEADD(YEAR, -1, GETDATE())
IF @MaximumDayDifference IS NULL
SET @MaximumDayDifference = 365
SET @RandomDate = CONVERT(DATE, DATEADD(DAY, ABS(CHECKSUM(NEWID()) % @MaximumDayDifference), @MinimumDate))
END
CREATE PROC [dbo].[GenerateRandomTime]
(
@RandomTime TIME OUTPUT,
@MaximumSecondsFromMidnight INT = NULL
)
AS
BEGIN
IF @MaximumSecondsFromMidnight IS NULL
SET @MaximumSecondsFromMidnight = 86400
SET @RandomTime = DATEADD(SECOND, RAND(CAST(NEWID() AS VARBINARY)) * @MaximumSecondsFromMidnight, CAST('00:00:00' AS TIME))
END
I created a function that calls these two SPs.
CREATE FUNCTION GenerateRandomDateTime
(
@MinimumDate DATETIME,
@MaximumDayDifference INT,
@MaximumSecondsFromMidnight INT
) RETURNS DATETIME
BEGIN
DECLARE @RandomDate DATETIME
EXEC GenerateRandomDate @RandomDate OUTPUT, @MinimumDate, @MaximumDayDifference
DECLARE @RandomTime TIME
EXEC GenerateRandomTime @RandomTime OUTPUT, @MaximumSecondsFromMidnight
RETURN @RandomDate + @RandomTime
END
I was wanting to make it a function so I could use it in an update statement directly. When I attempt to use the function, it states "Only functions and some extended stored procedures can be executed from within a function." Is there an alternative to what I am trying to do? Thanks in advance.
October 12, 2016 at 1:33 pm
Alaster07 (10/12/2016)
I am trying to create a function that will generate a random datetime value. Due to being unable to use NEWID() and RAND() in UDFs, I created two SPs to generate the date part and time part.
CREATE PROC [dbo].[GenerateRandomDate](
@RandomDate DATETIME OUTPUT,
@MinimumDate DATETIME = NULL,
@MaximumDayDifference INT = NULL
)
AS
BEGIN
IF @MinimumDate IS NULL
SET @MinimumDate = DATEADD(YEAR, -1, GETDATE())
IF @MaximumDayDifference IS NULL
SET @MaximumDayDifference = 365
SET @RandomDate = CONVERT(DATE, DATEADD(DAY, ABS(CHECKSUM(NEWID()) % @MaximumDayDifference), @MinimumDate))
END
CREATE PROC [dbo].[GenerateRandomTime](
@RandomTime TIME OUTPUT,
@MaximumSecondsFromMidnight INT = NULL
)
AS
BEGIN
IF @MaximumSecondsFromMidnight IS NULL
SET @MaximumSecondsFromMidnight = 86400
SET @RandomTime = DATEADD(SECOND, RAND(CAST(NEWID() AS VARBINARY)) * @MaximumSecondsFromMidnight, CAST('00:00:00' AS TIME))
END
I created a function that calls these two SPs.
CREATE FUNCTION GenerateRandomDateTime(
@MinimumDate DATETIME,
@MaximumDayDifference INT,
@MaximumSecondsFromMidnight INT
) RETURNS DATETIME
BEGIN
DECLARE @RandomDate DATETIME
EXEC GenerateRandomDate @RandomDate OUTPUT, @MinimumDate, @MaximumDayDifference
DECLARE @RandomTime TIME
EXEC GenerateRandomTime @RandomTime OUTPUT, @MaximumSecondsFromMidnight
RETURN @RandomDate + @RandomTime
END
I was wanting to make it a function so I could use it in an update statement directly. When I attempt to use the function, it states "Only functions and some extended stored procedures can be executed from within a function." Is there an alternative to what I am trying to do? Thanks in advance.
Not sure why you think you need to create the first two as procs. You aren't getting any performance benefit by creating them as procs. They are functions so just create them as such and then you can call them from your third function or if you don't want to create 3 functions you could integrate the two procs(functions) into your third.
October 12, 2016 at 1:40 pm
Correct me if I am wrong, but I am unable to use NEWID() and RAND() functions in UDFs.
October 12, 2016 at 2:23 pm
Alaster07 (10/12/2016)
Is there an alternative to what I am trying to do?
Not really. Keep in mind that a function must consistently return the same value for the same input values. SQL expects that behavior and will work incorrectly without it, since it will cache prior results to satisfy future calls to the function.
I think you'll need to use a stored proc. But, you could call the proc once and have it load a given number of random values into a table, which you could then use as you needed to, within say an inline-table-valued function.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 12, 2016 at 2:25 pm
Alaster07 (10/12/2016)
Correct me if I am wrong, but I am unable to use NEWID() and RAND() functions in UDFs.
You are correct. My Apologies as penance I offer this as a solution.
Create View rndView
AS
SELECT RAND() rndResult
GO
CREATE FUNCTION RandFn()
RETURNS DECIMAL(18,18)
AS
BEGIN
DECLARE @rndValue DECIMAL(18,18)
SELECT @rndValue = rndResult
FROM rndView
RETURN @rndValue
END
GO
CREATE FUNCTION GenerateRandomDateTime
(
@MinimumDate DATETIME,
@MaximumDayDifference INT,
@MaximumSecondsFromMidnight INT
) RETURNS DATETIME
BEGIN
DECLARE @RandomDate DATETIME
BEGIN
IF @MinimumDate IS NULL
SET @MinimumDate = DATEADD(YEAR, -1, GETDATE())
IF @MaximumDayDifference IS NULL
SET @MaximumDayDifference = 365
SET @RandomDate = (CONVERT(DATE, DATEADD(DAY, ( (dbo.RandFn()*@MaximumDayDifference) % @MaximumDayDifference), @MinimumDate)))
END
BEGIN
DECLARE @RandomTime TIME
IF @MaximumSecondsFromMidnight IS NULL
SET @MaximumSecondsFromMidnight = 86400
SET @RandomTime = (DATEADD(SECOND, ( dbo.RandFn() * @MaximumSecondsFromMidnight), CAST('00:00:00' AS TIME)))
END
RETURN @RandomDate + @RandomTime
END
SELECT dbo.GenerateRandomDateTime('01/01/2016 23:59:59',5,20000)
October 12, 2016 at 2:27 pm
Alaster07 (10/12/2016)
Correct me if I am wrong, but I am unable to use NEWID() and RAND() functions in UDFs.
Try an inline table-valued function instead.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 12, 2016 at 2:31 pm
The standard workaround is to create a view that uses RAND() and/or NEWID() and then select from the view in the UDF.
And the reason they can't be used is that they alter the state of the database. It has nothing to do with being non-deterministic.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 12, 2016 at 2:36 pm
Here's an alternative that should be faster than using a scalar function. It includes an example on how to use it.
CREATE VIEW vNEWID
AS
SELECT NEWID() AS _NewID;
GO
CREATE FUNCTION GenerateRandomDateTime
(
@MinimumDate DATETIME,
@MaximumDayDifference INT,
@MaximumSecondsFromMidnight INT
) RETURNS TABLE
AS
RETURN
SELECT DATEADD( SS,
ABS( CHECKSUM(v1._NewID)) % ISNULL(@MaximumSecondsFromMidnight, 86400),
DATEADD(DAY,
ABS(CHECKSUM(v2._NewID) % ISNULL(@MaximumDayDifference, 365)),
ISNULL(@MinimumDate, DATEADD(YEAR, DATEDIFF( YEAR, 0, GETDATE()), 0))
)
) RandomDate
FROM vNEWID v1, vNEWID v2;
GO
SELECT rndDT.*, t.*
FROM sys.tables t
CROSS APPLY GenerateRandomDateTime(t.create_date, max_column_id_used, NULL) rndDT;
GO
DROP FUNCTION GenerateRandomDateTime;
DROP VIEW vNEWID;
October 12, 2016 at 3:28 pm
Alaster07 (10/12/2016)
When I attempt to use the function, it states "Only functions and some extended stored procedures can be executed from within a function." Is there an alternative to what I am trying to do? Thanks in advance.
But you can select from a view within a function.
And view can contain RAND(), NEW_ID(), and all sorts of other things.
_____________
Code for TallyGenerator
October 12, 2016 at 4:02 pm
Based on the feedback in this thread, I have changed the SPs to UDFs and am using a view instead of calling the NEWID() and RAND() functions directly.
CREATE VIEW vSeedData
AS
SELECT NEWID() GuidSeed,
RAND(CAST(NEWID() AS VARBINARY)) DecimalSeed
CREATE FUNCTION [GenerateRandomDate]
(
@MinimumDate DATETIME,
@MaximumDayDifference INT
) RETURNS DATETIME
BEGIN
IF @MinimumDate IS NULL
SET @MinimumDate = DATEADD(YEAR, -1, GETDATE())
IF @MaximumDayDifference IS NULL
SET @MaximumDayDifference = 365
RETURN CONVERT(DATE, DATEADD(DAY, ABS(CHECKSUM((SELECT GuidSeed FROM vSeedData)) % @MaximumDayDifference), @MinimumDate))
END
CREATE FUNCTION [GenerateRandomTime]
(
@MaximumSecondsFromMidnight INT
) RETURNS TIME
BEGIN
IF @MaximumSecondsFromMidnight IS NULL
SET @MaximumSecondsFromMidnight = 86400
RETURN DATEADD(SECOND, (SELECT DecimalSeed FROM vSeedData) * @MaximumSecondsFromMidnight, CAST('00:00:00' AS TIME))
END
CREATE FUNCTION [GenerateRandomDateTime]
(
@MinimumDate DATETIME,
@MaximumDayDifference INT,
@MaximumSecondsFromMidnight INT
) RETURNS DATETIME
BEGIN
RETURN dbo.GenerateRandomDate(@MinimumDate, @MaximumDayDifference) + dbo.GenerateRandomTime(@MaximumSecondsFromMidnight)
END
Usage: SELECT dbo.GenerateRandomDateTime('2016-10-01', 12, NULL)
I wanted to keep them as three separate functions so I could also generate just a date and time separately without having to do extra casting.
Thanks to everyone that participated in this thread.
October 12, 2016 at 7:00 pm
Alaster07 (10/12/2016)
Based on the feedback in this thread, I have changed the SPs to UDFs and am using a view instead of calling the NEWID() and RAND() functions directly.
CREATE VIEW vSeedDataAS
SELECT NEWID() GuidSeed,
RAND(CAST(NEWID() AS VARBINARY)) DecimalSeed
CREATE FUNCTION [GenerateRandomDate](
@MinimumDate DATETIME,
@MaximumDayDifference INT
) RETURNS DATETIME
BEGIN
IF @MinimumDate IS NULL
SET @MinimumDate = DATEADD(YEAR, -1, GETDATE())
IF @MaximumDayDifference IS NULL
SET @MaximumDayDifference = 365
RETURN CONVERT(DATE, DATEADD(DAY, ABS(CHECKSUM((SELECT GuidSeed FROM vSeedData)) % @MaximumDayDifference), @MinimumDate))
END
CREATE FUNCTION [GenerateRandomTime](
@MaximumSecondsFromMidnight INT
) RETURNS TIME
BEGIN
IF @MaximumSecondsFromMidnight IS NULL
SET @MaximumSecondsFromMidnight = 86400
RETURN DATEADD(SECOND, (SELECT DecimalSeed FROM vSeedData) * @MaximumSecondsFromMidnight, CAST('00:00:00' AS TIME))
END
CREATE FUNCTION [GenerateRandomDateTime](
@MinimumDate DATETIME,
@MaximumDayDifference INT,
@MaximumSecondsFromMidnight INT
) RETURNS DATETIME
BEGIN
RETURN dbo.GenerateRandomDate(@MinimumDate, @MaximumDayDifference) + dbo.GenerateRandomTime(@MaximumSecondsFromMidnight)
END
Usage: SELECT dbo.GenerateRandomDateTime('2016-10-01', 12, NULL)
I wanted to keep them as three separate functions so I could also generate just a date and time separately without having to do extra casting.
Thanks to everyone that participated in this thread.
With these 3 functions as they are developed you're already doing many more CASTs than necessary.
Not to mention of overhead of every UTF (especially scalar UTF) call.
It's so significant then 100 extra CASTs cannot match it.
As well DATE and TIME data types - processing of values of those types is about 3 times slower comparing to DATETIME.
Try this approach:
ALTER VIEW vSeedData
AS
SELECT RAND(CHECKSUM(NEWID())) Seed
GO
alter FUNCTION [GenerateRandomDateTime]
(
@MinimumDate DATETIME,
@MaximumDayDifference INT,
@MaximumSecondsFromMidnight INT
) RETURNS DATETIME
BEGIN
RETURN(SELECT TOP 1 DATEADD(DAY, Seed * ISNULL(@MaximumDayDifference, 365), @MinimumDate) + DATEADD(SECOND, Seed * ISNULL(@MaximumSecondsFromMidnight, 86400), 0)
FROM vSeedData
)
END
GO
Now - if you want "dates only" - pass MaximumSecondsFromMidnight = 0.
If you need "time only" - SELECT dbo.GenerateRandomDateTime(0, 0, NULL).
You may convert (implicitly or explicitly ) the DATETIME value returned by the function to any data type you need at the end.
_____________
Code for TallyGenerator
October 12, 2016 at 11:38 pm
Sergiy (10/12/2016)
ALTER VIEW vSeedData
AS
SELECT RAND(CHECKSUM(NEWID())) Seed
GO
alter FUNCTION [GenerateRandomDateTime]
(
@MinimumDate DATETIME,
@MaximumDayDifference INT,
@MaximumSecondsFromMidnight INT
) RETURNS DATETIME
BEGIN
RETURN(SELECT TOP 1 DATEADD(DAY, Seed * ISNULL(@MaximumDayDifference, 365), @MinimumDate) + DATEADD(SECOND, Seed * ISNULL(@MaximumSecondsFromMidnight, 86400), 0)
FROM vSeedData
)
END
GO
Hi Sergiy
For my education .... What is the reason for the TOP 1 in the function?
October 12, 2016 at 11:59 pm
DesNorton (10/12/2016)
Sergiy (10/12/2016)
Try this approach:
ALTER VIEW vSeedDataAS
SELECT RAND(CHECKSUM(NEWID())) Seed
GO
alter FUNCTION [GenerateRandomDateTime]
(
@MinimumDate DATETIME,
@MaximumDayDifference INT,
@MaximumSecondsFromMidnight INT
) RETURNS DATETIME
BEGIN
RETURN(SELECT TOP 1 DATEADD(DAY, Seed * ISNULL(@MaximumDayDifference, 365), @MinimumDate) + DATEADD(SECOND, Seed * ISNULL(@MaximumSecondsFromMidnight, 86400), 0)
FROM vSeedData
)
END
GO
Hi Sergiy
For my education .... What is the reason for the TOP 1 in the function?
It's a scalar function, can return only 1 value.
If anyone modifies the view to return more than 1 Seed, the function would start failing, if TOP 1 would not be there.
_____________
Code for TallyGenerator
October 13, 2016 at 7:57 am
As previously pointed out, I strongly recommend conversion of the scalar functions to iTVFs (inline Table Valued Functions). Basically, if the function has a BEGIN in it, it's not an iTVF and will be slower than if it was.
Please see the following article for a demonstration of what I'm talking about.
How to Make Scalar UDFs Run Faster [/url]
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2016 at 10:07 am
I tried your approach, and it kind of mostly works. The issue I am running into is that the "early" times will all be at the front of the result set and all of the "late" times will be at the end of the result set.
dbo.GenerateRandomDateTime('2016-01-01', 287, 43200)
Here's the result set on ~10,000 rows. http://pastebin.com/8eVJFHWt
As you can see, the beginning of the year has the lower times and the end of the year has the higher times. I assume this is because both the date and time parts are using the same seed to "randomize"?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply