Text to Date conversion?

[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

[Herfried K. Wagner [MVP]]

Re: Text to Date conversion?

* "John Wildes" <spamthis@kisso ff.com> scripsit:[color=blue]
> 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.[/color]

What does "A" stand for? Have a look at 'DateTime.Parse ' and
'DateTime.Parse Exact' methods.

--
Herfried K. Wagner [MVP]
<http://www.mvps.org/dotnet>

[John Wildes]

Re: Text to Date conversion?

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


"Herfried K. Wagner [MVP]" <hirf-spam-me-here@gmx.at> wrote in message
news:bujpb5$i9n g3$3@ID-208219.news.uni-berlin.de...[color=blue]
> * "John Wildes" <spamthis@kisso ff.com> scripsit:[color=green]
> > We are querying transaction data from a DB3 database application. The[/color][/color]
dates[color=blue][color=green]
> > are stored as text fields. Each date for example 10/31/03 or October[/color][/color]
31st[color=blue][color=green]
> > 2003 is stored as 10/31/A3 in the system. My reasoning for this is[/color][/color]
because[color=blue][color=green]
> > they couldn't solve their Y2K problem or this is their solution to it.[/color][/color]
All[color=blue][color=green]
> > dates prior to 2000 are stored like actual dates, 10/31/98, 7/4/76, etc.[/color]
>
> What does "A" stand for? Have a look at 'DateTime.Parse ' and
> 'DateTime.Parse Exact' methods.
>
> --
> Herfried K. Wagner [MVP]
> <http://www.mvps.org/dotnet>[/color]

[Claes Bergefall]

Re: Text to Date conversion?

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.Repl ace("A", "200")
dateString = dateString.Repl ace("B", "201")
dateString = dateString.Repl ace("C", "202")
dateString = dateString.Repl ace("D", "203")
dateString = dateString.Repl ace("E", "204")
dateString = dateString.Repl ace("F", "205")
dateString = dateString.Repl ace("G", "206")
dateString = dateString.Repl ace("H", "207")
dateString = dateString.Repl ace("I", "208")
dateString = dateString.Repl ace("J", "209")

' Interpret a 2-digit year as a year between 1900 and 1999
Dim dateTimeFormat As System.Globaliz ation.DateTimeF ormatInfo = New
System.Globaliz ation.DateTimeF ormatInfo
Dim cal As System.Globaliz ation.Calendar = dateTimeFormat. Calendar
cal.TwoDigitYea rMax = 1999
dateTimeFormat. Calendar = cal

' Convert the string
Return Date.Parse(date String, dateTimeFormat,
Globalization.D ateTimeStyles.N one)
End Function

/claes


"John Wildes" <spamthis@kisso ff.com> wrote in message
news:efwaA633DH A.1752@tk2msftn gp13.phx.gbl...[color=blue]
> 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[/color]
it,[color=blue]
> but that would be wrong.
>
> I will look into those methods.
>
> Any other suggestions?
> john
>
>
> "Herfried K. Wagner [MVP]" <hirf-spam-me-here@gmx.at> wrote in message
> news:bujpb5$i9n g3$3@ID-208219.news.uni-berlin.de...[color=green]
> > * "John Wildes" <spamthis@kisso ff.com> scripsit:[color=darkred]
> > > We are querying transaction data from a DB3 database application. The[/color][/color]
> dates[color=green][color=darkred]
> > > are stored as text fields. Each date for example 10/31/03 or October[/color][/color]
> 31st[color=green][color=darkred]
> > > 2003 is stored as 10/31/A3 in the system. My reasoning for this is[/color][/color]
> because[color=green][color=darkred]
> > > they couldn't solve their Y2K problem or this is their solution to it.[/color][/color]
> All[color=green][color=darkred]
> > > dates prior to 2000 are stored like actual dates, 10/31/98, 7/4/76,[/color][/color][/color]
etc.[color=blue][color=green]
> >
> > What does "A" stand for? Have a look at 'DateTime.Parse ' and
> > 'DateTime.Parse Exact' methods.
> >
> > --
> > Herfried K. Wagner [MVP]
> > <http://www.mvps.org/dotnet>[/color]
>
>[/color]

[Jay B. Harlow [MVP - Outlook]]

Re: Text to Date conversion?

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.Str ingBuilder(text , text.Length *
3)
dateString.Repl ace("A", "200")
dateString.Repl ace("B", "201")
dateString.Repl ace("C", "202")
dateString.Repl ace("D", "203")
dateString.Repl ace("E", "204")
dateString.Repl ace("F", "205")
dateString.Repl ace("G", "206")
dateString.Repl ace("H", "207")
dateString.Repl ace("I", "208")
dateString.Repl ace("J", "209")

' Interpret a 2-digit year as a year between 1900 and 1999
Dim dateTimeFormat As System.Globaliz ation.DateTimeF ormatInfo = New
System.Globaliz ation.DateTimeF ormatInfo
Dim cal As System.Globaliz ation.Calendar = dateTimeFormat. Calendar
cal.TwoDigitYea rMax = 1999
dateTimeFormat. Calendar = cal

' Convert the string
Return Date.Parse(date String.ToString (), dateTimeFormat,
Globalization.D ateTimeStyles.N one)
End Function


"Claes Bergefall" <claes.bergefal l.nospam@fronte c.se> wrote in message
news:%23J$dmy$3 DHA.2432@TK2MSF TNGP09.phx.gbl. ..[color=blue]
> 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.Repl ace("A", "200")
> dateString = dateString.Repl ace("B", "201")
> dateString = dateString.Repl ace("C", "202")
> dateString = dateString.Repl ace("D", "203")
> dateString = dateString.Repl ace("E", "204")
> dateString = dateString.Repl ace("F", "205")
> dateString = dateString.Repl ace("G", "206")
> dateString = dateString.Repl ace("H", "207")
> dateString = dateString.Repl ace("I", "208")
> dateString = dateString.Repl ace("J", "209")
>
> ' Interpret a 2-digit year as a year between 1900 and 1999
> Dim dateTimeFormat As System.Globaliz ation.DateTimeF ormatInfo = New
> System.Globaliz ation.DateTimeF ormatInfo
> Dim cal As System.Globaliz ation.Calendar = dateTimeFormat. Calendar
> cal.TwoDigitYea rMax = 1999
> dateTimeFormat. Calendar = cal
>
> ' Convert the string
> Return Date.Parse(date String, dateTimeFormat,
> Globalization.D ateTimeStyles.N one)
> End Function
>
> /claes
>
>
> "John Wildes" <spamthis@kisso ff.com> wrote in message
> news:efwaA633DH A.1752@tk2msftn gp13.phx.gbl...[color=green]
> > 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[/color][/color]
that[color=blue][color=green]
> > 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[/color]
> it,[color=green]
> > but that would be wrong.
> >
> > I will look into those methods.
> >
> > Any other suggestions?
> > john
> >
> >
> > "Herfried K. Wagner [MVP]" <hirf-spam-me-here@gmx.at> wrote in message
> > news:bujpb5$i9n g3$3@ID-208219.news.uni-berlin.de...[color=darkred]
> > > * "John Wildes" <spamthis@kisso ff.com> scripsit:
> > > > We are querying transaction data from a DB3 database application.[/color][/color][/color]
The[color=blue][color=green]
> > dates[color=darkred]
> > > > are stored as text fields. Each date for example 10/31/03 or[/color][/color][/color]
October[color=blue][color=green]
> > 31st[color=darkred]
> > > > 2003 is stored as 10/31/A3 in the system. My reasoning for this is[/color]
> > because[color=darkred]
> > > > they couldn't solve their Y2K problem or this is their solution to[/color][/color][/color]
it.[color=blue][color=green]
> > All[color=darkred]
> > > > dates prior to 2000 are stored like actual dates, 10/31/98, 7/4/76,[/color][/color]
> etc.[color=green][color=darkred]
> > >
> > > What does "A" stand for? Have a look at 'DateTime.Parse ' and
> > > 'DateTime.Parse Exact' methods.
> > >
> > > --
> > > Herfried K. Wagner [MVP]
> > > <http://www.mvps.org/dotnet>[/color]
> >
> >[/color]
>
>[/color]

[John Wildes]

Re: Text to Date conversion?

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


"Jay B. Harlow [MVP - Outlook]" <Jay_Harlow_MVP @msn.com> wrote in message
news:%23IB8yXC4 DHA.2404@TK2MSF TNGP12.phx.gbl. ..[color=blue]
> Claes,
> With that number of replaces I would consider using a StringBuilder[/color]
instead.[color=blue]
> 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.Str ingBuilder(text , text.Length[/color]
*[color=blue]
> 3)
> dateString.Repl ace("A", "200")
> dateString.Repl ace("B", "201")
> dateString.Repl ace("C", "202")
> dateString.Repl ace("D", "203")
> dateString.Repl ace("E", "204")
> dateString.Repl ace("F", "205")
> dateString.Repl ace("G", "206")
> dateString.Repl ace("H", "207")
> dateString.Repl ace("I", "208")
> dateString.Repl ace("J", "209")
>
> ' Interpret a 2-digit year as a year between 1900 and 1999
> Dim dateTimeFormat As System.Globaliz ation.DateTimeF ormatInfo =[/color]
New[color=blue]
> System.Globaliz ation.DateTimeF ormatInfo
> Dim cal As System.Globaliz ation.Calendar = dateTimeFormat. Calendar
> cal.TwoDigitYea rMax = 1999
> dateTimeFormat. Calendar = cal
>
> ' Convert the string
> Return Date.Parse(date String.ToString (), dateTimeFormat,
> Globalization.D ateTimeStyles.N one)
> End Function
>
>
> "Claes Bergefall" <claes.bergefal l.nospam@fronte c.se> wrote in message
> news:%23J$dmy$3 DHA.2432@TK2MSF TNGP09.phx.gbl. ..[color=green]
> > 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.Repl ace("A", "200")
> > dateString = dateString.Repl ace("B", "201")
> > dateString = dateString.Repl ace("C", "202")
> > dateString = dateString.Repl ace("D", "203")
> > dateString = dateString.Repl ace("E", "204")
> > dateString = dateString.Repl ace("F", "205")
> > dateString = dateString.Repl ace("G", "206")
> > dateString = dateString.Repl ace("H", "207")
> > dateString = dateString.Repl ace("I", "208")
> > dateString = dateString.Repl ace("J", "209")
> >
> > ' Interpret a 2-digit year as a year between 1900 and 1999
> > Dim dateTimeFormat As System.Globaliz ation.DateTimeF ormatInfo = New
> > System.Globaliz ation.DateTimeF ormatInfo
> > Dim cal As System.Globaliz ation.Calendar = dateTimeFormat. Calendar
> > cal.TwoDigitYea rMax = 1999
> > dateTimeFormat. Calendar = cal
> >
> > ' Convert the string
> > Return Date.Parse(date String, dateTimeFormat,
> > Globalization.D ateTimeStyles.N one)
> > End Function
> >
> > /claes
> >
> >
> > "John Wildes" <spamthis@kisso ff.com> wrote in message
> > news:efwaA633DH A.1752@tk2msftn gp13.phx.gbl...[color=darkred]
> > > 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[/color][/color][/color]
files[color=blue][color=green][color=darkred]
> > > used in the FoxPro reporting tool and found a listing of conversions[/color][/color]
> that[color=green][color=darkred]
> > > 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[/color][/color][/color]
did[color=blue][color=green]
> > it,[color=darkred]
> > > but that would be wrong.
> > >
> > > I will look into those methods.
> > >
> > > Any other suggestions?
> > > john
> > >
> > >
> > > "Herfried K. Wagner [MVP]" <hirf-spam-me-here@gmx.at> wrote in message
> > > news:bujpb5$i9n g3$3@ID-208219.news.uni-berlin.de...
> > > > * "John Wildes" <spamthis@kisso ff.com> scripsit:
> > > > > We are querying transaction data from a DB3 database application.[/color][/color]
> The[color=green][color=darkred]
> > > dates
> > > > > are stored as text fields. Each date for example 10/31/03 or[/color][/color]
> October[color=green][color=darkred]
> > > 31st
> > > > > 2003 is stored as 10/31/A3 in the system. My reasoning for this[/color][/color][/color]
is[color=blue][color=green][color=darkred]
> > > because
> > > > > they couldn't solve their Y2K problem or this is their solution to[/color][/color]
> it.[color=green][color=darkred]
> > > All
> > > > > dates prior to 2000 are stored like actual dates, 10/31/98,[/color][/color][/color]
7/4/76,[color=blue][color=green]
> > etc.[color=darkred]
> > > >
> > > > What does "A" stand for? Have a look at 'DateTime.Parse ' and
> > > > 'DateTime.Parse Exact' methods.
> > > >
> > > > --
> > > > Herfried K. Wagner [MVP]
> > > > <http://www.mvps.org/dotnet>
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[yos yul]

Re: Text to Date conversion?


Convert date to number and number to date in VB.NET

Watch and copy examples of Source Code in VB.NET

http://www.run.to/yulyos-vbnetat

Have a nice day

From:
yulyos@yahoo.co m
http:/www.go.to/yulyos

http://www.run.to/yulyos-vbnet

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Jay B. Harlow [MVP - Outlook]]

Re: Text to Date conversion?

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.Selec t 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

"John Wildes" <spamthis@kisso ff.com> wrote in message
news:ul5FSSD4DH A.2888@tk2msftn gp13.phx.gbl...[color=blue]
> 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
>
>
> "Jay B. Harlow [MVP - Outlook]" <Jay_Harlow_MVP @msn.com> wrote in message
> news:%23IB8yXC4 DHA.2404@TK2MSF TNGP12.phx.gbl. ..[color=green]
> > Claes,
> > With that number of replaces I would consider using a StringBuilder[/color]
> instead.[color=green]
> > 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.Str ingBuilder(text ,[/color][/color]
text.Length[color=blue]
> *[color=green]
> > 3)
> > dateString.Repl ace("A", "200")
> > dateString.Repl ace("B", "201")
> > dateString.Repl ace("C", "202")
> > dateString.Repl ace("D", "203")
> > dateString.Repl ace("E", "204")
> > dateString.Repl ace("F", "205")
> > dateString.Repl ace("G", "206")
> > dateString.Repl ace("H", "207")
> > dateString.Repl ace("I", "208")
> > dateString.Repl ace("J", "209")
> >
> > ' Interpret a 2-digit year as a year between 1900 and 1999
> > Dim dateTimeFormat As System.Globaliz ation.DateTimeF ormatInfo =[/color]
> New[color=green]
> > System.Globaliz ation.DateTimeF ormatInfo
> > Dim cal As System.Globaliz ation.Calendar =[/color][/color]
dateTimeFormat. Calendar[color=blue][color=green]
> > cal.TwoDigitYea rMax = 1999
> > dateTimeFormat. Calendar = cal
> >
> > ' Convert the string
> > Return Date.Parse(date String.ToString (), dateTimeFormat,
> > Globalization.D ateTimeStyles.N one)
> > End Function
> >
> >
> > "Claes Bergefall" <claes.bergefal l.nospam@fronte c.se> wrote in message
> > news:%23J$dmy$3 DHA.2432@TK2MSF TNGP09.phx.gbl. ..[color=darkred]
> > > 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.Repl ace("A", "200")
> > > dateString = dateString.Repl ace("B", "201")
> > > dateString = dateString.Repl ace("C", "202")
> > > dateString = dateString.Repl ace("D", "203")
> > > dateString = dateString.Repl ace("E", "204")
> > > dateString = dateString.Repl ace("F", "205")
> > > dateString = dateString.Repl ace("G", "206")
> > > dateString = dateString.Repl ace("H", "207")
> > > dateString = dateString.Repl ace("I", "208")
> > > dateString = dateString.Repl ace("J", "209")
> > >
> > > ' Interpret a 2-digit year as a year between 1900 and 1999
> > > Dim dateTimeFormat As System.Globaliz ation.DateTimeF ormatInfo =[/color][/color][/color]
New[color=blue][color=green][color=darkred]
> > > System.Globaliz ation.DateTimeF ormatInfo
> > > Dim cal As System.Globaliz ation.Calendar = dateTimeFormat. Calendar
> > > cal.TwoDigitYea rMax = 1999
> > > dateTimeFormat. Calendar = cal
> > >
> > > ' Convert the string
> > > Return Date.Parse(date String, dateTimeFormat,
> > > Globalization.D ateTimeStyles.N one)
> > > End Function
> > >
> > > /claes
> > >
> > >
> > > "John Wildes" <spamthis@kisso ff.com> wrote in message
> > > news:efwaA633DH A.1752@tk2msftn gp13.phx.gbl...
> > > > A stands for I believe 200, and the sequence goes something like[/color][/color][/color]
this.[color=blue][color=green][color=darkred]
> > > >
> > > > A3=2003
> > > > B3=2013
> > > > C3=2023
> > > >
> > > > etc.
> > > >
> > > > My reason for this is because I ended up searching through the FPT[/color][/color]
> files[color=green][color=darkred]
> > > > used in the FoxPro reporting tool and found a listing of conversions[/color]
> > that[color=darkred]
> > > > apparently get read into memory when you run one of their reports.[/color][/color][/color]
It[color=blue][color=green][color=darkred]
> > > > actually calls a function called EIGHTTTOD, there is a foxpro[/color][/color][/color]
function[color=blue][color=green][color=darkred]
> > > > called TTOD , but I believe they wrote a function called EIGHTTTOD[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > > convert their dates. I thought about getting REFOX to see how they[/color][/color]
> did[color=green][color=darkred]
> > > it,
> > > > but that would be wrong.
> > > >
> > > > I will look into those methods.
> > > >
> > > > Any other suggestions?
> > > > john
> > > >
> > > >
> > > > "Herfried K. Wagner [MVP]" <hirf-spam-me-here@gmx.at> wrote in[/color][/color][/color]
message[color=blue][color=green][color=darkred]
> > > > news:bujpb5$i9n g3$3@ID-208219.news.uni-berlin.de...
> > > > > * "John Wildes" <spamthis@kisso ff.com> scripsit:
> > > > > > We are querying transaction data from a DB3 database[/color][/color][/color]
application.[color=blue][color=green]
> > The[color=darkred]
> > > > dates
> > > > > > are stored as text fields. Each date for example 10/31/03 or[/color]
> > October[color=darkred]
> > > > 31st
> > > > > > 2003 is stored as 10/31/A3 in the system. My reasoning for this[/color][/color]
> is[color=green][color=darkred]
> > > > because
> > > > > > they couldn't solve their Y2K problem or this is their solution[/color][/color][/color]
to[color=blue][color=green]
> > it.[color=darkred]
> > > > All
> > > > > > dates prior to 2000 are stored like actual dates, 10/31/98,[/color][/color]
> 7/4/76,[color=green][color=darkred]
> > > etc.
> > > > >
> > > > > What does "A" stand for? Have a look at 'DateTime.Parse ' and
> > > > > 'DateTime.Parse Exact' methods.
> > > > >
> > > > > --
> > > > > Herfried K. Wagner [MVP]
> > > > > <http://www.mvps.org/dotnet>
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[John Wildes]

Re: Text to Date conversion?

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


"Jay B. Harlow [MVP - Outlook]" <Jay_Harlow_MVP @msn.com> wrote in message
news:en35RoI4DH A.2616@TK2MSFTN GP09.phx.gbl...[color=blue]
> 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.Selec t on this extra
> column. Of course if the desired rows is significantly less then the[/color]
number[color=blue]
> of rows in the table, this is probably more pain then gain...
>
> Alternatively you could create a pseudo date that you use to compare[/color]
against[color=blue]
> 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
>
> "John Wildes" <spamthis@kisso ff.com> wrote in message
> news:ul5FSSD4DH A.2888@tk2msftn gp13.phx.gbl...[color=green]
> > What happens is I need to return a set of records based on a Customer[/color][/color]
Code[color=blue][color=green]
> > and a Date Range. I have code in place to select the records I need[/color][/color]
based[color=blue][color=green]
> > 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[/color][/color]
from[color=blue][color=green]
> > (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
> >
> >
> > "Jay B. Harlow [MVP - Outlook]" <Jay_Harlow_MVP @msn.com> wrote in[/color][/color]
message[color=blue][color=green]
> > news:%23IB8yXC4 DHA.2404@TK2MSF TNGP12.phx.gbl. ..[color=darkred]
> > > Claes,
> > > With that number of replaces I would consider using a StringBuilder[/color]
> > instead.[color=darkred]
> > > As the StringBuilder would not be creating 9 extra intermediate[/color][/color][/color]
strings,[color=blue][color=green][color=darkred]
> > > 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.Str ingBuilder(text ,[/color][/color]
> text.Length[color=green]
> > *[color=darkred]
> > > 3)
> > > dateString.Repl ace("A", "200")
> > > dateString.Repl ace("B", "201")
> > > dateString.Repl ace("C", "202")
> > > dateString.Repl ace("D", "203")
> > > dateString.Repl ace("E", "204")
> > > dateString.Repl ace("F", "205")
> > > dateString.Repl ace("G", "206")
> > > dateString.Repl ace("H", "207")
> > > dateString.Repl ace("I", "208")
> > > dateString.Repl ace("J", "209")
> > >
> > > ' Interpret a 2-digit year as a year between 1900 and 1999
> > > Dim dateTimeFormat As System.Globaliz ation.DateTimeF ormatInfo[/color][/color][/color]
=[color=blue][color=green]
> > New[color=darkred]
> > > System.Globaliz ation.DateTimeF ormatInfo
> > > Dim cal As System.Globaliz ation.Calendar =[/color][/color]
> dateTimeFormat. Calendar[color=green][color=darkred]
> > > cal.TwoDigitYea rMax = 1999
> > > dateTimeFormat. Calendar = cal
> > >
> > > ' Convert the string
> > > Return Date.Parse(date String.ToString (), dateTimeFormat,
> > > Globalization.D ateTimeStyles.N one)
> > > End Function
> > >
> > >
> > > "Claes Bergefall" <claes.bergefal l.nospam@fronte c.se> wrote in message
> > > news:%23J$dmy$3 DHA.2432@TK2MSF TNGP09.phx.gbl. ..
> > > > 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.Repl ace("A", "200")
> > > > dateString = dateString.Repl ace("B", "201")
> > > > dateString = dateString.Repl ace("C", "202")
> > > > dateString = dateString.Repl ace("D", "203")
> > > > dateString = dateString.Repl ace("E", "204")
> > > > dateString = dateString.Repl ace("F", "205")
> > > > dateString = dateString.Repl ace("G", "206")
> > > > dateString = dateString.Repl ace("H", "207")
> > > > dateString = dateString.Repl ace("I", "208")
> > > > dateString = dateString.Repl ace("J", "209")
> > > >
> > > > ' Interpret a 2-digit year as a year between 1900 and 1999
> > > > Dim dateTimeFormat As System.Globaliz ation.DateTimeF ormatInfo =[/color][/color]
> New[color=green][color=darkred]
> > > > System.Globaliz ation.DateTimeF ormatInfo
> > > > Dim cal As System.Globaliz ation.Calendar =[/color][/color][/color]
dateTimeFormat. Calendar[color=blue][color=green][color=darkred]
> > > > cal.TwoDigitYea rMax = 1999
> > > > dateTimeFormat. Calendar = cal
> > > >
> > > > ' Convert the string
> > > > Return Date.Parse(date String, dateTimeFormat,
> > > > Globalization.D ateTimeStyles.N one)
> > > > End Function
> > > >
> > > > /claes
> > > >
> > > >
> > > > "John Wildes" <spamthis@kisso ff.com> wrote in message
> > > > news:efwaA633DH A.1752@tk2msftn gp13.phx.gbl...
> > > > > A stands for I believe 200, and the sequence goes something like[/color][/color]
> this.[color=green][color=darkred]
> > > > >
> > > > > A3=2003
> > > > > B3=2013
> > > > > C3=2023
> > > > >
> > > > > etc.
> > > > >
> > > > > My reason for this is because I ended up searching through the FPT[/color]
> > files[color=darkred]
> > > > > used in the FoxPro reporting tool and found a listing of[/color][/color][/color]
conversions[color=blue][color=green][color=darkred]
> > > that
> > > > > apparently get read into memory when you run one of their reports.[/color][/color]
> It[color=green][color=darkred]
> > > > > actually calls a function called EIGHTTTOD, there is a foxpro[/color][/color]
> function[color=green][color=darkred]
> > > > > called TTOD , but I believe they wrote a function called EIGHTTTOD[/color][/color]
> to[color=green][color=darkred]
> > > > > convert their dates. I thought about getting REFOX to see how[/color][/color][/color]
they[color=blue][color=green]
> > did[color=darkred]
> > > > it,
> > > > > but that would be wrong.
> > > > >
> > > > > I will look into those methods.
> > > > >
> > > > > Any other suggestions?
> > > > > john
> > > > >
> > > > >
> > > > > "Herfried K. Wagner [MVP]" <hirf-spam-me-here@gmx.at> wrote in[/color][/color]
> message[color=green][color=darkred]
> > > > > news:bujpb5$i9n g3$3@ID-208219.news.uni-berlin.de...
> > > > > > * "John Wildes" <spamthis@kisso ff.com> scripsit:
> > > > > > > We are querying transaction data from a DB3 database[/color][/color]
> application.[color=green][color=darkred]
> > > 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[/color][/color][/color]
this[color=blue][color=green]
> > is[color=darkred]
> > > > > because
> > > > > > > they couldn't solve their Y2K problem or this is their[/color][/color][/color]
solution[color=blue]
> to[color=green][color=darkred]
> > > it.
> > > > > All
> > > > > > > dates prior to 2000 are stored like actual dates, 10/31/98,[/color]
> > 7/4/76,[color=darkred]
> > > > etc.
> > > > > >
> > > > > > What does "A" stand for? Have a look at 'DateTime.Parse ' and
> > > > > > 'DateTime.Parse Exact' methods.
> > > > > >
> > > > > > --
> > > > > > Herfried K. Wagner [MVP]
> > > > > > <http://www.mvps.org/dotnet>
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]