FoxPro & SQL Server Date field issues
Nick Ward
I am currently working on a large legacy Foxpro Application converting from
Fox2x into MS SQL Server version 6.5. I am employing pass-through SQL
exclusively.
The problem:
Under Foxpro (all versions) there is a field type called DATE. This field
type has no time component. When you add a 1 to a date variable it moves the
date on by one day. There is also a DATETIME field type. When you add 1 to a
datetime variable it moves on by one second.
SQL Server has no DATE field type, as a result all dates must be upsized as
DATETIME fields. When this data is retrieved from SQL Server into Foxpro
tables it is seen as datetime. The legacy code expects DATE data types and
therefore displays incorrectly and confuses days with seconds.
My solution:
Every pass-through SQL expression is encapsulated so I can look at the
results set and construct (in Foxpro) and empty table that has the same
structure except that type T columns are replaced with type D columns. I
then append the result set into the empty table and pretend this table is
the result set.
Questions:
Is there a more elegant way to achieve my data type conversion that does not
require the double handling used in my solution? Are there settings / data
types in SQL Server version 7 that solve my problem? Has anyone else
experienced this problem?
Further issues:
FoxPro date fields allow a blank value (all versions) as well as a null
value (under versions 3 to 6). SQL Server however only permits nulls. The
empty() function applied to a null variable returns a result of null (not
.T. as we might hope). As a result legacy code will fail to work. Would it
not be great if there was an option to assign how the SQL Server – Foxpro
interface was to treat nulls. Has anyone found a solution to this problem?
Nick Ward
OrionCA wrote
>
>Another solution:
>
>1. Import your tables.
>
>2. ALTER TABLE rem_table ALTER COLUMN date_field d not null
>
>3. Run your legacy code.
>
>Step 2 will convert all those nasty datetime fields into date fields
>and convert nulls to blanks. Then your legacy code should run
>properly.
>--
Thank you for this suggestion.
Unfortunately the ALTER TABLE command has two problems.
1) It will only operate on one column at a time. If you have three datetime
fields you
must issue three separate ALTER TABLE commands. This is unacceptable for all
but the smallest record sets.
2) A cursor returned from a SQLEXEC command is read-writable but has the
curious attribute of being a "remote table". Clearly it is not a remote
table but the ALTER TABLE command believes it to be one and refuses to
operate on it. It is however possible to copy/append the data into another
table and perform the ALTER TABLE command there. This would involve a double
handling that does not avoid the problem as originally raised.
As far as I know the only (single statement) way to change null dates into
blanks is through the ALTER TABLE command. So this suggestion at least
attempts to solve both problems at once.
Does anyone else have a suggestion to make?
Nick Ward
arosser
Can you not simply select ttod(dateitmeField) as newName
Andrew R.
Oyvind Snibsoer
your query. E.g. to return an ISO format date-expression like '19981117',
use SELECT date = CONVERT(CHAR(8), datetime_field, 112) FROM mytable. Check
ut the CONVERT function in the T-SQL helpfile
/Oyvind Snibsoer
Anders Altberg
and column names, except Datetime is changed to date. Append from the SPT
result set. AFIELDS makes it easy if the structure isn't known already.
-Anders
Nick Ward wrote in message <72riom$h40$1...@taliesin.netcom.net.uk>...
Nick Ward
arosser wrote in message <36516807...@promtek.com>...
>Nick,
>
>Can you not simply select ttod(dateitmeField) as newName
>
Ahh if only...
TTOD is specific to Foxpro and their is no DateTime to Date conversion
function in SQL Server as it does not know of Date type. There is a general
conversion function in SQL Server called Convert(). This is the subject of
another reply to this problem and I will discuss its disadvantages as a
reply to that message.
Thanks
Nick Ward
Nick Ward
Oyvind Snibsoer wrote in message <72rrmd$4pk$1...@elle.eunet.no>...
We are getting closer...
The SQL Server function Convert will convert a DateTime value into another
type. Clearly this must be a type that SQL Server knows about. There can be
no simple conversion to Date type as SQL Server does not about Date types.
In your example you used CHAR(8). This means the date columns will appear in
a FoxPro Cursor as a C(8) column.
A legacy application (which expects Dates) will not be able to work with a
character field in the same way as a Date column. Take for example
NextDate = Cursor.date + 1
or
dtoc(Cursor.date)
What is needed is a 'conversion style' that Foxpro sees as a date column
instead of a character column. Perhaps an undocumented feature might be
lurking somewhere?
Nick Ward
Anders Altberg wrote in message ...
>Create another cursor on the fly, or a view, with the identical structure
>and column names, except Datetime is changed to date. Append from the SPT
>result set. AFIELDS makes it easy if the structure isn't known already.
>-Anders
>
Thanks Anders
This solution is actually the one I have implemented. It is as efficient as
it is possible to get given that this double handling cannot be avoided. My
hope is that it can be avoided.
By the way I can change the setting regarding whether nulls are allowed.
When I do that the append fails to work. Append will convert DateTime values
into Date values but it will not convert null datetime values into blank
date values. Other than that it works well on small record sets.
Nick Ward
Anders Altberg
If you do it like this instead of using Append
SELET sqlcursor
SCAN
INSERT INTO <view> VALUES ( NVL( datetime, {}) )
ENDSCAN
NULL and conversion is taken care of in one go.
-Anders
Nick Ward wrote in message <72s4d0$94u$1...@taliesin.netcom.net.uk>...
Nick Ward
Anders Altberg wrote in message ...
>If you do it like this instead of using Append
>SELET sqlcursor
>SCAN
> INSERT INTO <view> VALUES ( NVL( datetime, {}) )
>ENDSCAN
>
>NULL and conversion is taken care of in one go.
>-Anders
>
Yes this does the job.
I Prefer to use INSERT INTO ... FROM MEMVAR and change the nulls while
dealing with memory variables.
I guess this thread has run its course.
It seems there is NO WAY to see Date columns come out of SQL Server EXCEPT
by DOUBLE HANDLING every record-set that has DateTime columns present.
It seems there is NO WAY to see blank Date values come out of SQL Server
EXCEPT by DOUBLE HANDLING every record-set that has null DateTime values in
it.
As far as I can tell this does not change in the apparently "wonderful"
version 7. So much for backward compatibility.
:(
Thanks to all
Nick Ward
Anders Altberg
This works too:
create cursor test (dd c(10) null)
insert into test values ("1998/10/22")
insert into test values (null)
alter table test alter column dd d
So if you can get SS to CONVERT and insert date marks, either / or -, alter
table would do it.
When one appends from delimited text FoxPro converts string like 19981221
correctly to dates.
And why not as that's exactly how dates are actually stored in a dbf.
-Anders
Nick Ward wrote in message <72uqvr$sfr$1...@taliesin.netcom.net.uk>...
Nick Ward
Anders Altberg wrote in message ...
>This works too:
>create cursor test (dd c(10) null)
>insert into test values ("1998/10/22")
>insert into test values (null)
>alter table test alter column dd d
>
>So if you can get SS to CONVERT and insert date marks, either / or -, alter
>table would do it.
>When one appends from delimited text FoxPro converts string like 19981221
>correctly to dates.
>And why not as that's exactly how dates are actually stored in a dbf.
>-Anders
>
Operationally this seems to solve all problems EXCEPT performance. ALTER
TABLE is not just double handling it is N fold handling (where N is the
number of DateTime columns). Wouldn't it be great if Alter table had only to
change some bytes in the header...No it processes the entire file regardless
of how close to the "native" datatype the original column is.
Thanks
Nick Ward
Anders Altberg
Get dates converted by SS to character 19980121, copy to sdf, append to date
field. You'll have automatic conversion in VFP.
create cursor temp (date c(8))
insert into temp values ("19981221")
insert into temp values ("19980102")
copy to temp.txt sdf
create cursor temp2 (date d)
set date ymd
set century on
append from temp.txt sdf
set date american
list off
12/21/1998
01/02/1998
-Anders
Nick Ward wrote in message <731qej$ms8$1...@taliesin.netcom.net.uk>...
Nick Ward
Anders Altberg wrote in message ...
>Get dates converted by SS to character 19980121, copy to sdf, append to
date
>field. You'll have automatic conversion in VFP.
>
>create cursor temp (date c(8))
>insert into temp values ("19981221")
>insert into temp values ("19980102")
>copy to temp.txt sdf
>create cursor temp2 (date d)
>set date ymd
>set century on
>append from temp.txt sdf
>set date american
>list off
> 12/21/1998
> 01/02/1998
>
I am confused as to the point you are making. While this may save some
"steps" this is just a variation on Appending, which is just a variation on
the original solution which involves "double handling". While some double
handling routines may be slighter faster than others, they are far short of
getting SQL Server (somehow) to produce a cursor with Date columns in a
single operation. i.e. an operation that does not require every record to be
re-processed.
I would expect Microsoft to have some mechanism, given its commitment to SQL
Server and the business need for backward compatibility. Is FoxPro the only
language which legitimately uses Date variables? Surely this problem occurs
in VB as well?
Nick Ward
Anders Altberg
-Anders
Nick Ward wrote in message <733mi8$923$1...@taliesin.netcom.net.uk>...