Text to Date conversion?

  • Thread starter Thread starter John Wildes
  • Start date Start date
J

John Wildes

Hello all

I'm going to try and be brief with my question, please tell me if I have the
wrong group.

We are querying transaction data from a DB3 database application. The dates
are stored as text fields. Each date for example 10/31/03 or October 31st
2003 is stored as 10/31/A3 in the system. My reasoning for this is because
they couldn't solve their Y2K problem or this is their solution to it. All
dates prior to 2000 are stored like actual dates, 10/31/98, 7/4/76, etc.

What we need to do is query the data based on a date range, return a record
set or records including the date in each record and display it on a crystal
report, using the crystal report viewer.

How do I convert the dates information stored like this 10/31/A3 to 10/31/03
? I am extremely new to programming and vb in general, and I am working on
a deadline. This is my only major problem at this time. I need to convert
these dates and store them temporarily in memory so I can pass them on to
the crystal report.

If anyone feels they can help please do, if there is more information needed
please ask. I will do what I can, in the mean time I am studying up on
CDate and similar functions.

Thanks
john
 
* "John Wildes said:
We are querying transaction data from a DB3 database application. The dates
are stored as text fields. Each date for example 10/31/03 or October 31st
2003 is stored as 10/31/A3 in the system. My reasoning for this is because
they couldn't solve their Y2K problem or this is their solution to it. All
dates prior to 2000 are stored like actual dates, 10/31/98, 7/4/76, etc.
Click to expand...

What does "A" stand for? Have a look at 'DateTime.Parse' and
'DateTime.ParseExact' methods.
 
A stands for I believe 200, and the sequence goes something like this.

A3=2003
B3=2013
C3=2023

etc.

My reason for this is because I ended up searching through the FPT files
used in the FoxPro reporting tool and found a listing of conversions that
apparently get read into memory when you run one of their reports. It
actually calls a function called EIGHTTTOD, there is a foxpro function
called TTOD , but I believe they wrote a function called EIGHTTTOD to
convert their dates. I thought about getting REFOX to see how they did it,
but that would be wrong.

I will look into those methods.

Any other suggestions?
john
 
This seems to work:

Private Function Text2Date(ByVal text As String) As Date
' Replace "A" with "200", "B" with "201", "C" with "202" etc
Dim dateString As String = text
dateString = dateString.Replace("A", "200")
dateString = dateString.Replace("B", "201")
dateString = dateString.Replace("C", "202")
dateString = dateString.Replace("D", "203")
dateString = dateString.Replace("E", "204")
dateString = dateString.Replace("F", "205")
dateString = dateString.Replace("G", "206")
dateString = dateString.Replace("H", "207")
dateString = dateString.Replace("I", "208")
dateString = dateString.Replace("J", "209")

' Interpret a 2-digit year as a year between 1900 and 1999
Dim dateTimeFormat As System.Globalization.DateTimeFormatInfo = New
System.Globalization.DateTimeFormatInfo
Dim cal As System.Globalization.Calendar = dateTimeFormat.Calendar
cal.TwoDigitYearMax = 1999
dateTimeFormat.Calendar = cal

' Convert the string
Return Date.Parse(dateString, dateTimeFormat,
Globalization.DateTimeStyles.None)
End Function

/claes


John Wildes said:
A stands for I believe 200, and the sequence goes something like this.

A3=2003
B3=2013
C3=2023

etc.

My reason for this is because I ended up searching through the FPT files
used in the FoxPro reporting tool and found a listing of conversions that
apparently get read into memory when you run one of their reports. It
actually calls a function called EIGHTTTOD, there is a foxpro function
called TTOD , but I believe they wrote a function called EIGHTTTOD to
convert their dates. I thought about getting REFOX to see how they did it,
but that would be wrong.

I will look into those methods.

Any other suggestions?
john
Click to expand...
 
Claes,
With that number of replaces I would consider using a StringBuilder instead.
As the StringBuilder would not be creating 9 extra intermediate strings,
there would be the StringBuilder & the returned String...

Something like:

Private Shared Function Text2Date(ByVal text As String) As Date
' Replace "A" with "200", "B" with "201", "C" with "202" etc
Dim dateString As New System.Text.StringBuilder(text, text.Length *
3)
dateString.Replace("A", "200")
dateString.Replace("B", "201")
dateString.Replace("C", "202")
dateString.Replace("D", "203")
dateString.Replace("E", "204")
dateString.Replace("F", "205")
dateString.Replace("G", "206")
dateString.Replace("H", "207")
dateString.Replace("I", "208")
dateString.Replace("J", "209")

' Interpret a 2-digit year as a year between 1900 and 1999
Dim dateTimeFormat As System.Globalization.DateTimeFormatInfo = New
System.Globalization.DateTimeFormatInfo
Dim cal As System.Globalization.Calendar = dateTimeFormat.Calendar
cal.TwoDigitYearMax = 1999
dateTimeFormat.Calendar = cal

' Convert the string
Return Date.Parse(dateString.ToString(), dateTimeFormat,
Globalization.DateTimeStyles.None)
End Function


Claes Bergefall said:
This seems to work:

Private Function Text2Date(ByVal text As String) As Date
' Replace "A" with "200", "B" with "201", "C" with "202" etc
Dim dateString As String = text
dateString = dateString.Replace("A", "200")
dateString = dateString.Replace("B", "201")
dateString = dateString.Replace("C", "202")
dateString = dateString.Replace("D", "203")
dateString = dateString.Replace("E", "204")
dateString = dateString.Replace("F", "205")
dateString = dateString.Replace("G", "206")
dateString = dateString.Replace("H", "207")
dateString = dateString.Replace("I", "208")
dateString = dateString.Replace("J", "209")

' Interpret a 2-digit year as a year between 1900 and 1999
Dim dateTimeFormat As System.Globalization.DateTimeFormatInfo = New
System.Globalization.DateTimeFormatInfo
Dim cal As System.Globalization.Calendar = dateTimeFormat.Calendar
cal.TwoDigitYearMax = 1999
dateTimeFormat.Calendar = cal

' Convert the string
Return Date.Parse(dateString, dateTimeFormat,
Globalization.DateTimeStyles.None)
End Function

/claes
Click to expand...
 
What happens is I need to return a set of records based on a Customer Code
and a Date Range. I have code in place to select the records I need based
on customer code, and what I am working at now is how do I filter these
records on a date range, which means I would have to convert the date from
(ex. 01/01/A4) to (ex. 01/01/04) in place before it is returned to the
listview that I have created, and then pass the data on in the correct
format i.e. 01/01/04 to the crystal report. The data doesn't have to be
saved anywhere and we do not need to update the system. Any idea how I
would do that? This is primarily to create a statement report.

Thanks for any input.

john
 
John,
If I am following your question, I would strongly suggest you correct the
database itself.

Is the database itself going to stay FoxPro, or are you converting it to
another DB?

If you are converting the database to another DB, then I would change the
column to a real date field at that time.

If you are not converting to something other then FoxPro, I would consider
defining a view or SP in FoxPro (does FoxPro support stored procedures?)
that presented a real date to your program.

How many rows are we talking about, you could retrieve all the rows into a
DataTable, add an extra column for the date, run Claes's routine for each
row updating the extra column, then do a DataTable.Select on this extra
column. Of course if the desired rows is significantly less then the number
of rows in the table, this is probably more pain then gain...

Alternatively you could create a pseudo date that you use to compare against
the database..

Mostly, I would strongly suggest you correct the database!!!

Note: I have not really used Foxpro, so I'm not sure if a stored procedure
is supported...

Hope this helps
Jay
 
Jay,

I wish that I could correct the database, but it the application that uses
it is written by a third party, who admits that this was their lazy way of
solving the Y2K problem rather than changing the fields in their data base
to support date format.

(Anyone ever hear of "The Agency Manager" by Applied Systems ???? this is
the app that I am talking about)

The database itself is DB3 and I am using a FoxPro driver to access it
because the DB3 driver takes forever to return a simple query. There are
only 70,000 records in the transact dbf file, and I am only looking at a
smaller subset of those records, possibly no more than 1000.

FoxPro does support stored procedures based on a FoxPro DBC file which
basically is a file that links DBFs. If I add the tables from this
application to the FoxPro DBC it corrupts them so the application cannot use
them. Using a stored procedure might get really messy.

I will look into creating a datatable, with an additional row to convert the
proper date. This isn't more hassle than it's worth for me. We need to get
this done, and I am willing to try anything right now.

More importantly, thanks for all the help. I will let you know how this
comes out.
john
 
Back
Top