Customize DateTime Generator MinValue/MaxValue

• 
  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 June 04, 2010 08:27

  - 0 +
• 
  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
  

  tkdennis June 04, 2010 14:08

  - 0 +

Add comment

Please sign in to leave a comment.