Customize DateTime Generator MinValue/MaxValue

The simplest way to populate your database with test data.

Moderators: David Atkinson, Anu Deshpande, Lionel

Customize DateTime Generator MinValue/MaxValue

Postby tkdennis » Thu Jun 03, 2010 2:12 am

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?

Code: Select all
   <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
tkdennis
 
Posts: 75
Joined: Fri Feb 29, 2008 1:13 am

Postby Brian Donahue » Fri Jun 04, 2010 1:27 pm

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.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby tkdennis » Fri Jun 04, 2010 7:08 pm

That worked perfectly - thank you!

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

Code: Select all
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
tkdennis
 
Posts: 75
Joined: Fri Feb 29, 2008 1:13 am


Return to SQL Data Generator 1

Who is online

Users browsing this forum: No registered users and 0 guests