Converting to a foxpro date time field 2

[dracos2]

I am having trouble getting a field (start_time) from a MSSQL 2000 table (Data type nvarchar) 20040204 00:01:27.803 to a FoxPro 7.0 Table That I want to use as a Date time field. When I change the data type in the foxpro table to DATETIME I either get no data in the start_time field. I though the CTOT would work but I can not figure where to use it or if It realy is correct


USE samupdate ORDER start_time IN 0

=STATMSG("Getting data from the M2K...&quot
sSQL="SELECT status,publication,subscriber,subscription_type,start_time,time,duration,comments,error_id,user_name"
sSQL=sSQL+" FROM tbldistribution_agent"
x=SQLConn("NetReports&quot
SQLExec(x,sSQL,"Updating&quot
SQLDisc(x)



=STATMSG("Updating the table...&quot
SELECT Updating
LOCATE
SCAN
m.publicatio=publication
m.subscripti=subscription_type
WAIT WINDOW NOWAIT "Updating Record "+ALLT(STR(RECNO()))+" of "+ALLT(STR(RECCOUNT()))
SCATTER MEMVAR
IF SEEK(start_time,"samupdate&quot
SELECT samupdate
GATHER MEMVAR
SELECT Updating

ELSE

INSERT INTO samupdate FROM MEMVAR
ENDIF
ENDSCAN
WAIT CLEAR

 

[NoWayIsThisInUse]

A Foxpro datetime field has the format:
"01/01/2000 06:00:00 PM" so you'll need to convert your sql field to a character in this same format and then do the ctot() function to go from character to datetime.

 

[DSummZZZ]

You just need to shift things around a little:

cDateTime = start_time  &&...ex: '20040204 00:01:27.803'
start_time = Ctot(Substr(cDateTime, 5, 2) + '/' + ;
      Substr(cDateTime, 7, 2) + '/' + ;
      Substr(cDateTime, 1, 4) + ;
      Substr(cDateTime, 9))

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[dracos2]

Dave I apologize but where would I place this to allow the conversion on the field to take place. I will have to change the criteria on the if statements for updating and inserting also because the time stamp is the only unique item in the record.

 

[DSummZZZ]

Your "Updating" table has the SQL extracted data, and your "samupdate" table has the data to be updated.
So what you need to do is convert the SQL data to something the "samupdate" table can use.
But an issue arises when you have two tables with the same 'common' field name but with different data types, i.e., start_time.
One thing about Foxpro that can be either a blessing or a curse, is that it isn't strong typed. Types can change on the fly, without having been predefined.
So, you can use start_time as a character type variable in your scatter, convert it to datetime, and use it for the gather.
I WOULD NOT ADVISE DOING THIS!!!
You really should have a different field name on your tables, but as a band-aid, the following code should work. Did I mention that I would advise against it?:

=STATMSG("Updating the table...")
SELECT Updating
LOCATE
SCAN
   m.publicatio=publication
   m.subscripti=subscription_type
   WAIT WINDOW NOWAIT "Updating Record "+ALLT(STR(RECNO()))+" of "+ALLT(STR(RECCOUNT()))
   SCATTER MEMVAR
[COLOR=blue]
   cDateTime = start_time  &&...ex: '20040204 00:01:27.803'
   start_time = Ctot(Substr(cDateTime, 5, 2) + '/' + ;
         Substr(cDateTime, 7, 2) + '/' + ;
         Substr(cDateTime, 1, 4) + ;
         Substr(cDateTime, 9))
[/color]   
   IF SEEK(start_time,"samupdate")
      SELECT samupdate
      GATHER MEMVAR
      SELECT Updating

   ELSE

      INSERT INTO samupdate FROM MEMVAR
   ENDIF
ENDSCAN
WAIT CLEAR

Sorry to be so long-winded, but oh well. I just hope its clear how it works.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[terosoft]

Hi,
In your code where it says:
sSQL="SELECT status, publication, subscriber, subscription_type, start_time, time, duration, comments, error_id, user_name"

Change it to:

sSQL="SELECT status, publication, subscriber, subscription_type, CAST(start_time AS datetime) AS start_time , time, duration, comments, error_id, user_name"

Doing this SQL will convert your varchar field into a datetime one.

If you want more control on the datetime style you can also use CONVERT() instead of CAST(), more information on SQL help or MSDN

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

Hope this helps,

Gerardo Czajkowski

 

[dracos2]

Thanks for the help