Red Gate forums :: View topic - Customize DateTime Generator MinValue/MaxValue
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  | Register 
Go to product documentation
SQL Data Generator 1
SQL Data Generator 1 forum

Customize DateTime Generator MinValue/MaxValue

 Search in SQL Data Generator 1 forum
Post new topic   Reply to topic
Jump to:  
Author Message
tkdennis



Joined: 29 Feb 2008
Posts: 74
PostPosted: Thu Jun 03, 2010 2:12 am    Post subject: Customize DateTime Generator MinValue/MaxValue Reply with quote
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:
   <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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6369
Location: Red Gate Software
PostPosted: Fri Jun 04, 2010 1:27 pm    Post subject: Reply with quote
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/DateTimeToTicks.aspx

I hope this works for you.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521
Back to top
View user's profile Send private message
tkdennis



Joined: 29 Feb 2008
Posts: 74
PostPosted: Fri Jun 04, 2010 7:08 pm    Post subject: Reply with quote
That worked perfectly - thank you!

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

Code:
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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
 You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group