Solved

Posted on 2006-05-23

I've been tasked with converting some VERY old archived DBF databases to SQL Server 2000/2005. I've managed to convert most of the data, except the dates are stored in some strange fashion that I haven't been able to solve yet. The dates in the DBF file are all char(8).

We have an old DOS application that actually converts these strange dates to a modern date, but the DOS program needs to go and the data needs to be converted.

Here are a few examples:

DBF char(8) Actual Date/Time

01JB8APK 12/23/2005 12:29:44pm

01JB8AS4 12/23/2005 12:31:16pm

01JGKNFK 04/06/2006 09:23:44am

01JGKNSX 04/06/2006 09:31:45am

01JGKNX3 04/06/2006 09:34:15am

01JGKNZ4 04/06/2006 09:35:28am

I have plenty more if you need them. I don't have enough DBF experience to get this one. Please help! Maxed points for quick resolution.

We have an old DOS application that actually converts these strange dates to a modern date, but the DOS program needs to go and the data needs to be converted.

Here are a few examples:

DBF char(8) Actual Date/Time

01JB8APK 12/23/2005 12:29:44pm

01JB8AS4 12/23/2005 12:31:16pm

01JGKNFK 04/06/2006 09:23:44am

01JGKNSX 04/06/2006 09:31:45am

01JGKNX3 04/06/2006 09:34:15am

01JGKNZ4 04/06/2006 09:35:28am

I have plenty more if you need them. I don't have enough DBF experience to get this one. Please help! Maxed points for quick resolution.

10 Comments

(13 * 36) + 13 = 481

I'll play with it a bit

I have no info or code on the DOS program. I'm following you so far on the conversion, but it is new me. What would happen if the difference in dates needed an extra digit and went beyond the (13*36)+13?

taking the example '01JB8APK'

K = 20 * (36^0) = 20

P = 25 * (36^1) = 900

A = 10 * (36^2) = 1296

8 = 8 * (36^3) = 373248

B = 11 * (36^4) = 18475776

J = 19 * (36^5) = 1148857344

1 = 1 * (36^6) = 2176782336

0 = 0 * (36^7) = 0

for a total of 3344490920 (assuming my math is right)

If you add these seconds to the appropriate start date (the epoch date) then you will get the value you are looking for. The problem I am having is arithmetic overflow in T-SQL when trying to increment the date by a value greater than maxint. Unfortunately none of the functions I have tried take a bigint value. I am still working on a work around.

Here is what I have so far...I make an intial guess of 1/1/1900 for the epoch date

DECLARE @chardate char(8),

@power tinyint,

@date datetime,

@seconds bigint,

@count tinyint

SET @chardate = '01JB8APK'

SET @power = 0

SET @date = '19000101'

WHILE @power < LEN(@chardate)

BEGIN

SET @count = 0

SET @seconds = 1

WHILE @count < @power

BEGIN

SET @seconds = @seconds * CAST(36 as bigint)

SET @count = @count + 1

END

SET @seconds = @seconds *

CASE SUBSTRING(@chardate, LEN(@chardate) - @power, 1)

WHEN '0' THEN 0

WHEN '1' THEN 1

WHEN '2' THEN 2

WHEN '3' THEN 3

WHEN '4' THEN 4

WHEN '5' THEN 5

WHEN '6' THEN 6

WHEN '7' THEN 7

WHEN '8' THEN 8

WHEN '9' THEN 9

WHEN 'A' THEN 10

WHEN 'B' THEN 11

WHEN 'C' THEN 12

WHEN 'D' THEN 13

WHEN 'E' THEN 14

WHEN 'F' THEN 15

WHEN 'G' THEN 16

WHEN 'H' THEN 17

WHEN 'I' THEN 18

WHEN 'J' THEN 19

WHEN 'K' THEN 20

WHEN 'L' THEN 21

WHEN 'M' THEN 22

WHEN 'N' THEN 23

WHEN 'O' THEN 24

WHEN 'P' THEN 25

WHEN 'Q' THEN 26

WHEN 'R' THEN 27

WHEN 'S' THEN 28

WHEN 'T' THEN 29

WHEN 'U' THEN 30

WHEN 'V' THEN 31

WHEN 'W' THEN 32

WHEN 'X' THEN 33

WHEN 'Y' THEN 34

WHEN 'Z' THEN 35

END

SET @date = DATEADD(second, @seconds, @date)

PRINT @seconds

PRINT @date

SET @power = @power + 1

END

DECLARE @chardate char(8),

@power tinyint,

@date datetime,

@seconds bigint,

@count tinyint,

@maxint int

SET @chardate = '01JGKNFK'

SET @power = 0

SET @date = '18991230'

SET @maxint = 2147483647

WHILE @power < LEN(@chardate)

BEGIN

SET @count = 0

SET @seconds = 1

WHILE @count < @power

BEGIN

SET @seconds = @seconds * CAST(36 as bigint)

SET @count = @count + 1

END

SET @seconds = @seconds *

CASE SUBSTRING(@chardate, LEN(@chardate) - @power, 1)

WHEN '0' THEN 0

WHEN '1' THEN 1

WHEN '2' THEN 2

WHEN '3' THEN 3

WHEN '4' THEN 4

WHEN '5' THEN 5

WHEN '6' THEN 6

WHEN '7' THEN 7

WHEN '8' THEN 8

WHEN '9' THEN 9

WHEN 'A' THEN 10

WHEN 'B' THEN 11

WHEN 'C' THEN 12

WHEN 'D' THEN 13

WHEN 'E' THEN 14

WHEN 'F' THEN 15

WHEN 'G' THEN 16

WHEN 'H' THEN 17

WHEN 'I' THEN 18

WHEN 'J' THEN 19

WHEN 'K' THEN 20

WHEN 'L' THEN 21

WHEN 'M' THEN 22

WHEN 'N' THEN 23

WHEN 'O' THEN 24

WHEN 'P' THEN 25

WHEN 'Q' THEN 26

WHEN 'R' THEN 27

WHEN 'S' THEN 28

WHEN 'T' THEN 29

WHEN 'U' THEN 30

WHEN 'V' THEN 31

WHEN 'W' THEN 32

WHEN 'X' THEN 33

WHEN 'Y' THEN 34

WHEN 'Z' THEN 35

END

IF @seconds > @maxint

BEGIN

SET @date = DATEADD(second, @maxint, @date)

SET @date = DATEADD(second, @seconds - @maxint, @date)

END

ELSE

SET @date = DATEADD(second, @seconds, @date)

SET @power = @power + 1

END

PRINT CONVERT(varchar, @date, 120)

give the points to BriCrowe

isn't 18991230 the default/null VB date...

declare @d datetime

declare @x char(8)

declare @i bigint,@q bigint

declare @idx int

declare @y char(1)

set @i=0

set @x='01JB8APK'

set @idx = Len(@x)

set @q=36

set @d='18991230'

while @idx > 0

begin

set @y=substring(@x,@idx,1)

select @i = @i + (case isnumeric(@y) when 0

then ascii(@y) - 55

else convert(tinyint,@y)

end) * power(@q,len(@x) - @idx)

,@idx=@idx-1

end

Select @idx = @i / 2147483647 , @i = @i % 2147483647

While @idx > 0

begin

select @d = DateAdd(s,2147483647,@d),@

end

set @d = Dateadd(s,@i,@d)

Print convert(varchar(26),@d)

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Transactional Replication using SQL Server 2008 | 20 | 30 | |

SQL 2014 Availability groups and listeners | 5 | 21 | |

Import csv files to MS SQL | 5 | 35 | |

Date Differences SSRS | 3 | 24 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**20** Experts available now in Live!