DateTime

[roger9991]

I have a table with date and time as separate fields. I want to transfer the information to another table which has a DateTime field. How do I merge the date and time from one table to put it in one field in the second table?

 

[Olaf Doschke]

There is no time field type in foxpro, so what field type is the time stored in? Is it a numeric value, as returned by Seconds(), or is it a string, as returned by Time()?

If its seconds you can merge this by

DTOT(dateportion)+timeportion.

If it's a time string, you can use it within a datetime literal in the form {^yyyy-mm-dd hh:mm:ss} and use EVALUATE() to convert such a literal to a datetime value.

Steps involved are converting the date to a string in the format yyyy-mm-dd, which almost can be done by DTOC(), but this depends on DATE settings, eg SET MARK TO.

If you want th econde working under any circumstance you use DTOC(datevalue,1), which yields a string in the format 'YYYYMMDD' and add hyphens with Transform() via format code "@R XXXX-XX-XX". All in all:

Evaluate("{^"+Transform(Dtoc(Date(),1),"@R XXXX-XX-XX")+" "+Time()+"}")

Replace Date() with the date field and replace Time() with your time field, and this will convert these seperate fields to one datetime value.

Another of course would be to convert the time string into seconds, eg via

DTOT(Date())+Val(GetWordnum(Time(),1,":"))*60*60+Val(GetWordnum(Time(),2,":"))*60+Val(GetWordnum(Time(),3,":"))

Again replace Date() with the date field and each occurrence of Time() with the time field.

I'd prefer the solution with {^yyyy-mm-dd hh:mm:ss}.

Bye, Olaf.

 

[roger9991]

The time was stored in a character field. It was added much later since initially all the info was in a memo field in table with a date field. Thank you for your response and help.

 

[Olaf Doschke]

OK, so in case you have no time, you should also handle this and just convert the date via DTOT(datefield) without adding time.

For example with IIF(EMPTY(NVL(timefield,'')),DTOT(datefield),<<...lengthyexpressionconvertingdateandtimetodatetime...>>)

Bye, Olaf.