What are the challenges you face when working across database platforms? Take the survey
Options

Customize DateTime Generator MinValue/MaxValue

tkdennistkdennis Posts: 114
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

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • Options
    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
    
Sign In or Register to comment.