D2007

We are converting data for a new customer.  The old data has a field for 
'updt-dt' which is a Date field.  The old data has a field for 'updt-tm' 
which is an Integer field.  We need to combine to have a DateTimeStamp.

var
  OldDate, OldTime : TDateTime;

looping

        FieldNameFrom := 'updt-dt';
        FieldNameFrom2 := 'updt-tm';
        if (not FieldByName(FieldNameFrom).IsNull) and
         (not FieldByName(FieldNameFrom2).IsNull) then
          begin
            Day := 0;
            Month := 0;
            Year := 0;
            Hour := 0;
            Min := 0;
            Sec := 0;
            MSec := 0;
            OldDate := FieldByName(FieldNameFrom).Value;
            DecodeDate(OldDate, Year, Month, Day);
            OldTime := FieldByName(FieldNameFrom2).Value;
            DecodeTime(OldTime, Hour, Min, Sec, MSec);
            Edit;
            FieldByName('LastModified').AsDateTime :=
              EncodeDateTime(Year, Month, Day, Hour, Min, Sec, 0);
            Post;
            Inc(i);
          end;

DecodeTime returns 0 for Hour, Min, ...

Thanks,
Mark

"Mark A. Smith" <mas@creativeautomation.net> wrote in message 
news:204812@forums.codegear.com...
> D2007
>
> We are converting data for a new customer.  The old data has a field for
> 'updt-dt' which is a Date field.  The old data has a field for 'updt-tm'
> which is an Integer field.  We need to combine to have a DateTimeStamp.
>
> var
>  OldDate, OldTime : TDateTime;
>
> looping
>
>        FieldNameFrom := 'updt-dt';
>        FieldNameFrom2 := 'updt-tm';
>        if (not FieldByName(FieldNameFrom).IsNull) and
>         (not FieldByName(FieldNameFrom2).IsNull) then
>          begin
>            Day := 0;
>            Month := 0;
>            Year := 0;
>            Hour := 0;
>            Min := 0;
>            Sec := 0;
>            MSec := 0;
>            OldDate := FieldByName(FieldNameFrom).Value;
>            DecodeDate(OldDate, Year, Month, Day);
>            OldTime := FieldByName(FieldNameFrom2).Value;

Time is the fractional portion of a TDateTime. Here, you are assigning it 
the actual integer value of the field. You would need to know how the time 
was converted into an integer for storage and then reverse that before using 
the DecodeTime function.

HTH

Woody (TMW)

I know that DateTime is stored as a FloatingPoint number.  If you examine a
TimeStamp field using FieldName.Value you can see the number.  The data I
have to convert has 2 columns, one is Date and the other is Integer.  I've
tried to combine the fields including by using date.value as string, add the
decimal point, and add time integer as string, then convert using FloatToStr
but it yields a zero.

Mark

"James Woodard" <woody-tmw@gt.rr.com> wrote in message 
news:204832@forums.codegear.com...
> "Mark A. Smith" <mas@creativeautomation.net> wrote in message
> news:204812@forums.codegear.com...
>> D2007
>>
>> We are converting data for a new customer.  The old data has a field for
>> 'updt-dt' which is a Date field.  The old data has a field for 'updt-tm'
>> which is an Integer field.  We need to combine to have a DateTimeStamp.
>>
>> var
>>  OldDate, OldTime : TDateTime;
>>
>> looping
>>
>>        FieldNameFrom := 'updt-dt';
>>        FieldNameFrom2 := 'updt-tm';
>>        if (not FieldByName(FieldNameFrom).IsNull) and
>>         (not FieldByName(FieldNameFrom2).IsNull) then
>>          begin
>>            Day := 0;
>>            Month := 0;
>>            Year := 0;
>>            Hour := 0;
>>            Min := 0;
>>            Sec := 0;
>>            MSec := 0;
>>            OldDate := FieldByName(FieldNameFrom).Value;
>>            DecodeDate(OldDate, Year, Month, Day);
>>            OldTime := FieldByName(FieldNameFrom2).Value;
>
> Time is the fractional portion of a TDateTime. Here, you are assigning it
> the actual integer value of the field. You would need to know how the time
> was converted into an integer for storage and then reverse that before 
> using
> the DecodeTime function.
>
> HTH
>
> Woody (TMW)

"Mark A. Smith" <mas@creativeautomation.net> wrote in message 
news:205283@forums.codegear.com...
>I know that DateTime is stored as a FloatingPoint number.  If you examine a
> TimeStamp field using FieldName.Value you can see the number.  The data I
> have to convert has 2 columns, one is Date and the other is Integer.  I've
> tried to combine the fields including by using date.value as string, add 
> the
> decimal point, and add time integer as string, then convert using 
> FloatToStr
> but it yields a zero.
>

What range of values are in the integer time fields? It sounds as though 
they are something other than the fractional part of a Delphi DateTime.

Can you determine, for example, what values are stored for a few sample 
known times? (e.g. 08.00, 12:00, 18:00). That might help to determine the 
format.

Regards, Paul.

"Mark A. Smith" <mas@creativeautomation.net> wrote in message 
news:205283@forums.codegear.com...
> I know that DateTime is stored as a FloatingPoint number.  If you examine 
> a
> TimeStamp field using FieldName.Value you can see the number.  The data I
> have to convert has 2 columns, one is Date and the other is Integer.  I've
> tried to combine the fields including by using date.value as string, add 
> the
> decimal point, and add time integer as string, then convert using 
> FloatToStr
> but it yields a zero.
>

I assume you mean StrToFloat, not FloatToStr, since you are going from a 
string to a float.

Regardless, as Paul has suggested, taking a look at some known times in the 
database may offer a clue as to how they were converted and stored. Also, 
you could post a few of the examples of the values for each field to see if 
we have the same problem converting them.

Woody (TMW)

Thanks guys, yes StrToFloat.

Record    DateField        Integer(For Time)
#1           04/07/2009    36097
#2           03/16/2009    48257
#3           04/13/2009    52856
and 9000 more similar to these, with the range somewhere between 30k and 55k

"Mark A. Smith" <mas@creativeautomation.net> wrote in message 
news:205283@forums.codegear.com...
>I know that DateTime is stored as a FloatingPoint number.  If you examine a
> TimeStamp field using FieldName.Value you can see the number.  The data I
> have to convert has 2 columns, one is Date and the other is Integer.  I've
> tried to combine the fields including by using date.value as string, add 
> the
> decimal point, and add time integer as string, then convert using 
> FloatToStr
> but it yields a zero.
>
> Mark
>
> "James Woodard" <woody-tmw@gt.rr.com> wrote in message
> news:204832@forums.codegear.com...
>> "Mark A. Smith" <mas@creativeautomation.net> wrote in message
>> news:204812@forums.codegear.com...
>>> D2007
>>>
>>> We are converting data for a new customer.  The old data has a field for
>>> 'updt-dt' which is a Date field.  The old data has a field for 'updt-tm'
>>> which is an Integer field.  We need to combine to have a DateTimeStamp.
>>>
>>> var
>>>  OldDate, OldTime : TDateTime;
>>>
>>> looping
>>>
>>>        FieldNameFrom := 'updt-dt';
>>>        FieldNameFrom2 := 'updt-tm';
>>>        if (not FieldByName(FieldNameFrom).IsNull) and
>>>         (not FieldByName(FieldNameFrom2).IsNull) then
>>>          begin
>>>            Day := 0;
>>>            Month := 0;
>>>            Year := 0;
>>>            Hour := 0;
>>>            Min := 0;
>>>            Sec := 0;
>>>            MSec := 0;
>>>            OldDate := FieldByName(FieldNameFrom).Value;
>>>            DecodeDate(OldDate, Year, Month, Day);
>>>            OldTime := FieldByName(FieldNameFrom2).Value;
>>
>> Time is the fractional portion of a TDateTime. Here, you are assigning it
>> the actual integer value of the field. You would need to know how the 
>> time
>> was converted into an integer for storage and then reverse that before
>> using
>> the DecodeTime function.
>>
>> HTH
>>
>> Woody (TMW)

Are the time values in the range 0-86399 ?
If so they're the number of seconds per day (86400)

"Mark A. Smith" <mas@creativeautomation.net> wrote in message 
news:205317@forums.codegear.com...
> Thanks guys, yes StrToFloat.
>
> Record    DateField        Integer(For Time)
> #1           04/07/2009    36097

Just testing the first one worked fine for me. However, looking back at your 
original post, you are using DecodeTime which expects a floating point 
number which includes time, not an integer. The integer is considered to be 
the date portion. Only the fraction is used for time.

Try using something like:

    DecodeTime(StrToFloat('.' + IntToStr(OldTime)), Hour, Minute, Second);

Maybe some else has a better way of writing it but it should at least show 
you a time if it was just saved as an integer instead of a decimal number.

Woody (TMW)

> {quote:title=James Woodard wrote:}{quote}
> "Mark A. Smith" <mas@creativeautomation.net> wrote in message 
> news:205317@forums.codegear.com...
> > Thanks guys, yes StrToFloat.
> >
> > Record    DateField        Integer(For Time)
> > #1           04/07/2009    36097
> 
> Just testing the first one worked fine for me. However, looking back at your 
> original post, you are using DecodeTime which expects a floating point 
> number which includes time, not an integer. The integer is considered to be 
> the date portion. Only the fraction is used for time.
> 
> Try using something like:
> 
>     DecodeTime(StrToFloat('.' + IntToStr(OldTime)), Hour, Minute, Second);
> 
> Maybe some else has a better way of writing it but it should at least show 
> you a time if it was just saved as an integer instead of a decimal number.
> 
> Woody (TMW)

IntToStr doesn't make a whole lot of sense without knowing how the data has been stored
Would a value of 1 mean 0.1, 0.01,  0.001 etc

Find the smallest and largest value in the data and you should be able to work it out from there
Knowing the values for a file you know the date/time of would be useful

"karl pritchett" wrote in message news:205424@forums.codegear.com...
>> {quote:title=James Woodard wrote:}{quote}
>> Try using something like:
>>
>>     DecodeTime(StrToFloat('.' + IntToStr(OldTime)), Hour, Minute, 
>> Second);
>>
>> Maybe some else has a better way of writing it but it should at least 
>> show
>> you a time if it was just saved as an integer instead of a decimal 
>> number.
>>
>> Woody (TMW)
>
> IntToStr doesn't make a whole lot of sense without knowing how the data 
> has been stored
> Would a value of 1 mean 0.1, 0.01,  0.001 etc

I already suggested that the OP needed to know how the time was converted 
before being saved. I was mainly showing them that they were trying to 
convert the time portion in the wrong way, as an integer, instead of a 
fraction. Without knowing what scale the time was multiplied by in the first 
place, it all becomes moot, as you point out.

Woody (TMW)

Thanks, got it working.
I was so close and yet so far!
Mark

"karl pritchett" wrote in message news:205424@forums.codegear.com...
>> {quote:title=James Woodard wrote:}{quote}
>> "Mark A. Smith" <mas@creativeautomation.net> wrote in message
>> news:205317@forums.codegear.com...
>> > Thanks guys, yes StrToFloat.
>> >
>> > Record    DateField        Integer(For Time)
>> > #1           04/07/2009    36097
>>
>> Just testing the first one worked fine for me. However, looking back at 
>> your
>> original post, you are using DecodeTime which expects a floating point
>> number which includes time, not an integer. The integer is considered to 
>> be
>> the date portion. Only the fraction is used for time.
>>
>> Try using something like:
>>
>>     DecodeTime(StrToFloat('.' + IntToStr(OldTime)), Hour, Minute, 
>> Second);
>>
>> Maybe some else has a better way of writing it but it should at least 
>> show
>> you a time if it was just saved as an integer instead of a decimal 
>> number.
>>
>> Woody (TMW)
>
> IntToStr doesn't make a whole lot of sense without knowing how the data 
> has been stored
> Would a value of 1 mean 0.1, 0.01,  0.001 etc
>
> Find the smallest and largest value in the data and you should be able to 
> work it out from there
> Knowing the values for a file you know the date/time of would be useful