Customize DateTime Generator MinValue/MaxValue

tkdennis

I'm trying to use the DateTimeGenerator.xml in the UserExample\config directory to make an specific date range generator. How do I convert a date to the MinValue and MaxValue numbers? Is there a formula to use?

<generator
type="RedGate.SQLDataGenerator.Generators.DateTime.DateTimeGenerator"
		name="DateTimeGenerator"
		description="DateTimeGenerator..."
		category="MyApp">

		<property name="MinValue">633032064000000000</property>
		<property name="MaxValue">633346560000000000</property>

Thanks,
Traci

Brian Donahue

The minimum and maximum value for the DateTime generator should be expressed in .NET Ticks. If you're stuck, it looks like SQL Server has a handy function to convert a datetime expression to ticks: dbo.DateTimeToTicks

http://www.codeproject.com/KB/database/ ... Ticks.aspx

I hope this works for you.

tkdennis

That worked perfectly - thank you!

I'm copying the code I used from the article and the comments, if anyone else needs it:

CREATE FUNCTION [dbo].GetTicksFromTime (@d datetime)
RETURNS BIGINT AS BEGIN 
	RETURN (DATEDiff(s, '20060823', @d) + 63291888000 ) * 10000000
END
GO

CREATE FUNCTION dbo.GetTimeFromTicks (@Ticks BIGINT)
RETURNS DATETIME AS BEGIN

	DECLARE @Days BIGINT
	DECLARE @DaysBefore1753 BIGINT
	DECLARE @TimeTicks BIGINT
	DECLARE @Seconds BIGINT

	SET @Days = @Ticks / CONVERT(BIGINT,864000000000)
	SET @DaysBefore1753 = CONVERT(BIGINT,639905)
	SET @TimeTicks = @Ticks % CONVERT(BIGINT,864000000000)
	SET @Seconds = @TimeTicks / CONVERT(BIGINT,10000000)

	RETURN DATEADD(s,@Seconds,DATEADD(d,@Days - @DaysBefore1753,CONVERT(DATETIME,'1/1/1753')))
END 
GO

DECLARE @TestDate datetime
SET @TestDate = GETDATE()
select @TestDate, dbo.GetTicksFromTime(@TestDate), dbo.GetTimeFromTicks(dbo.GetTicksFromTime(@TestDate))
SET @TestDate = '2/29/1992 12:34:56'
select @TestDate, dbo.GetTicksFromTime(@TestDate), dbo.GetTimeFromTicks(dbo.GetTicksFromTime(@TestDate))
GO