2

I need help. Pervasive DB stored dates in days. For example 719311 would be the amount of days from 01/01/0001 to May 28 1970. So May 28 1970 is the date represented in pervasive when looking at 719311.

SqlZim
  • 37,248
  • 6
  • 41
  • 59
developeranymous
  • 21
  • 1

3 Answers3

0
select datediff(day,'0001-01-01','1753-01-01') + 2

select 719311 - 639907 -- sql 

select dateadd(day,79404,'1753-01-01')

dateadd() function cannot work with a date older than '1753-01-01', so you need the 719311 days minus the 639907 days. The difference you can than plug into the dateadd() function.

You should get this 1970-05-28 00:00:00.000

SqlZim
  • 37,248
  • 6
  • 41
  • 59
RoganRicheart
  • 151
  • 1
  • 4
  • This worked. Just used it inline with the data being imported like DATEADD(DAY,COLUMNNAME - 639907,'1753-01-01') and the dates showed up. Thanks – developeranymous Feb 23 '17 at 17:25
0

You can use SQL Servers dateadd feature for this. However the values in Pervasive are much too large to add to the base date of 0 (1900-01-01).

To work around that I used the known integer/date provided and calculated the delta between 0001-01-01 and 1900-01-01 - which is 693597. This value is static, and can be subtracted from each Pervasive value and used in the dateadd. The result can then be added to 1900-01-01.

-- GET BASE DATE OF 0
SELECT CONVERT(DATETIME,0)

-- CALCULATE DIFF FROM 0 TO A KNOWN DATE...
SELECT DATEDIFF(DD,0,'1970-05-28')

--SUBTRACT ABOVE DIFF FROM KNOWN INTEGER FOR SAID DATE... THIS IS OUR DELTA
SELECT 719311-25714

-- ADD THE INTEGER OF SAID DATE, MINUS THE ABOVE DELTA TO 0 TO CONFIRM WE GET THE KNOWN DATE.
SELECT DATEADD(DD,719311-693597,0)

-- USING VARIABLES, LEAVE @DELTA AS A STATIC VALUE, JUST UPDATE @PERVASIVE
DECLARE @PERVASIVE INT, @DELTA INT
SET @PERVASIVE=719312

SET @DELTA=693597
SELECT DATEADD(DD,@PERVASIVE-@DELTA,0)
Dave C
  • 7,272
  • 1
  • 19
  • 30
  • I would recommend using `day` instead of `dd`. [Bad Habits to Kick : Using shorthand with date/time operations - Aaron Bertrand](https://sqlblog.org/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations) – SqlZim Feb 23 '17 at 17:38
  • I'm not Aaron, @AaronBertrand is the author of that article. – SqlZim Feb 23 '17 at 17:42
  • Thank you for that @SqlZim! – Dave C Feb 23 '17 at 17:44
0

Another alternative is add days to a date datatype.

When I add 719311 days to '0001-01-01' I get '1970-05-30' To get '1970-05-28' I have to remove 2 of those days.

select dateadd(day,719311-2,convert(date,'00010101'))

returns '1970-05-28'

SqlZim
  • 37,248
  • 6
  • 41
  • 59