?
Solved

Converting strange DBF date to something SQL Server can use

Posted on 2006-05-23
10
Medium Priority
?
339 Views
Last Modified: 2008-02-26
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.
0
Comment
Question by:LMITAdmin
  • 5
  • 4
10 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16743912
Can you provide any information on how the DOS program is doing the conversion?
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16743972
It appears it is using a base 36 system (zero - nine - A - Z) to store the number of seconds from some epoch date.  There are 481 seconds between '04/06/2006 09:23:44 am' and '04/06/2006 09:31:45 am'.  '01JGKNSX' - '01JGKNFK' = 481 where X - K = 13 and S - F = 13.

(13 * 36) + 13 = 481
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16743987
You would need to write a user-defined function to convert the string into the appropriate number of seconds and then convert that to a datetime datatype once you've established the epoch date.

I'll play with it a bit
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:LMITAdmin
ID: 16744029
BriCrowe - sorry...turned away for a second and you had replied.

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?
0
 

Author Comment

by:LMITAdmin
ID: 16744077
Let me clarify.  If for instance I have Z - A = 25 and Z - A = 25 would equal (25 * 36) + 25 = 925...but what then would it be for 926 seconds?  Forgive me...I failed the whole base 36 stuff in school.  :-)  I'm ok with base 10 and base 2...hehehe.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16744341
each digit represents a power of 36.

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
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 2000 total points
ID: 16744463
This seems to work...you could easily convert it to a user-defined function.  The epoch date appears to be 12/30/1899 which I thought was a bit odd but it works for all the examples you give.

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)
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16744753
interesting

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),@idx=@idx-1
 end  
 set @d = Dateadd(s,@i,@d)

Print convert(varchar(26),@d)
0
 

Author Comment

by:LMITAdmin
ID: 16744799
TSQL can only hold 68 years worth of seconds.  Using 69 years as a cutoff, so we'll get nowhere close to an overflow, substract 2175984000 from the value.
0
 

Author Comment

by:LMITAdmin
ID: 16744830
Nevermind...I like your idea better...forget the subtraction.

Thanks to you both!  Points go to BriCrowe.  Just a tad too late LowFat.  Thanks!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question