Converting Date field and Time field to DateTime

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
0
Mark
1/20/2010 2:53:24 PM
embarcadero.delphi.database 1294 articles. 0 followers. Follow

10 Replies
1991 Views

Similar Articles

[PageSpeed] 49

"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)
0
James
1/20/2010 3:14:32 PM
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)
0
Mark
1/21/2010 2:22:37 PM
"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.
0
Paul
1/21/2010 3:25:03 PM
"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)
0
James
1/21/2010 3:50:13 PM
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)
0
Mark
1/21/2010 4:03:01 PM
Are the time values in the range 0-86399 ?
If so they're the number of seconds per day (86400)
0
karl
1/21/2010 4:14:59 PM
"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)
0
James
1/21/2010 7:18:32 PM
> {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
0
karl
1/21/2010 8:14:45 PM
"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)
0
James
1/22/2010 3:10:42 PM
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
0
Mark
1/27/2010 12:26:48 PM
Reply:

Similar Artilces:

Why are DATE-fields converted to TIMESTAMP-fields?
Hi, I tried this in the powerJ newsgroup without any luck. When looking at the field type in ISQL or the powerJ-debug log I see that fields which in ASA are defined as DATE (or TIME) are seen as TIMESTAMP in powerj (or ISQL), eg. a field RegDate is a DATE and the log is as follows: ------------ powersoft.powerj.db.java_sql.ExtendedResultSetMetaData: column 14: Name='REGDATE',SQLName='REGDATE',Table='',Type=93 (TIMESTAMP), PrimaryKey=false,ReadOnly=false,AutoIncrement=false,Searchable=true,Nullable =0(no),Schema='',Catalog='',Scale=0,Precisio...

Validate date field, and time field
I've got a form with 2 textboxes 1 for a date, the validator needs to check if he has this kind of imput: dd-MM-yyyy1 for the time, this one needs to check if he has this kind of imput: MM:HH But how can i do that?  You can either write a custom validator or you can use the AjaxControlToolkit MaskedEdit control:http://www.asp.net/AJAX/AjaxControlToolkit/Samples/MaskedEdit/MaskedEdit.aspx I just used them in my last project and they did the job they needed to. I was a bit reserved about the way it works when you replace characters but I decided in normal use this wouldn...

How to convert a date field to short date? without time.
I am using a query builder to buld a sql statement which brings me somes fields which are defined as date, and they are brought like this one: 5/17/2006 12:00:00 AM and I just want to bring the 5/17/2006.I tried CONVERT (CHAR(10), Fecha_evento, 101) AS Fecha_evento  It works, for displaying, but when I try to look for the maximun within that field, it interprets it as a string, so I do not get the right value.  Do you have a way to solve this? thanks a lot! There's really no such thing as a date without a time.  If you're having problems with displaying the information ...

just Time or date portion of a date/time field
Good Day, From a DateTime data column how do I get just a Time portin or just a date portion of data to be displayed. i.e from 11/6/2003 10:03:20 AM I need to display date and time seperately in my data display page. Thanks Regards, In code you could use Dim dt as System.Date Dim day, month, year as integer day = dt.Day month= dt.Month year = dt.Year This also works with DateTime In SQL you could use DATEPART(<datepart>,<datetime>) where <datepart> is the abbreviations mentioned in post 385301 and <datetime> is a...

Update field field value at specified date and time....
I am doing one project in that...... after login user set one date and time...........it will store in one sql table.......... now user logout from the page also website ..... but now i want to update one field value to 1 but on specified date and time which was entered by user so many days ago..... Please help....... i know the code to update sql table but where to write that code(fire on that particular date and time) and  that i dont know..........Mayursinh B Rana9898010015Marking a Reply as 'Answered', not only GAIN us some POINTS, but it also HELP others ...

DATE field to DATE/TIME
Hello, If the field is defined as a DATE and you do: select datefield+1 from mytable Does the ODBC API (actually ASA 9.02 backend) return a field that is DATETIME or a DATE field? It is messing up my compiled application and seems to be coming back as DATETIME. Thank you. -Robert You are correct, the value is being returned as a TIMESTAMP. You can verify this through using the EXPRTYPE() builtin function, as in select exprtype('select datefield+1 from mytable', 1) from dummy which will return TIMESTAMP. Exprtype is documented in the help. You alm...

Date entered...add time value to make date/time field
Windows XP Professional InfoMaker v 9.0.2 Build 7509 Our database contains a date/time stamp for when a specific activity happens to each shipment. I would like the users to be able to simply enter a date for a specific work day. I need to get a list of shipments where the time of one activity occurred on the entered date prior to 9:20. How can I create a computed field that will basically concatenate/amend the entered date with 9:20 that will later be used for criteria in returning rows? Thanks in advance for any help you can provide! Rita Palazzi Senior Engineer /...

just Time or date portion of date/time field: SOMEBODY PLS
Good Day, From a DateTime data column how do I get just a Time portin or just a date portion of data to be displayed. i.e from 11/6/2003 10:03:20 AM I need to display date and time seperately in my data display page. Thanks Regards, When you pull in a SQL datetime column value into your .NET code, it is usually cast to a .NET DateTime type... something like this: System.DateTime TheDateAndTime = (System.DateTime)someDataReader["MySQLDateColumn"] Then you can use a variety of .NET methods included in the DateTime class to convert to a strin...

UTC date/time for membership database fields
Do you think the UTC date/time should be stored in the membership database columns named... CreateDate LastLoginDate LastPasswordChangedDate ....and any other date/time columns? I think the machine date/time is being used and this won't localize as easily as using something like getutcdate() in SQL Server will. Do you agree? I don't agree, it should be the date you have on the server. You can easy use the DateTime class to get the UTC time./Fredrik Normén - fredrikn @ twitterMicrosoft MVP, MCSD, MCAD, MCTASPInsidersMy Blog...

Issue: How to convert a database field from varchar to datetime..?
Can anyone help me on this!I've got more than a 1000 records in  a SQL server database.The problem is that the the date field is set to varchar, and that gives a lot of trouble. (for example by sorting a table, it's a mess)How can i make sure that i will have a table with the date field set to datettime en that those 1000 records still will be in it. thanks in advance! You should be able to just convert them to datetime select cast(varDateField as dateTime) from yourTable. The only problem you will have is if you dont have date fields in any of the data.Nick...

what default date to use in database datetime field
We want to add a default date to our database tables. Looking at other database samples people use all sorts of dates to add as default date e.g. 1/1/1997 or the getdate() function. Is it good practice to set a default date and what should the default date be???? Newbie All you need to know about SQL Server DateTime is covered in the link below.  Hope this helps. http://www.karaszi.com/SQLServer/info_datetime.aspKind regards,Gift Peddie Hello, As per my experience with datetime, I would prefer setting a default date so that "null" reference is avoided in the code. I dont use get...

saving ONLY date in database with DateTime field type
i wanna save date in database in the field with datatype datetime... can i do it and get ONLY DATE saved in database? i tried it in many ways but cant get rid of the time that is gettinf saved along with the date. i know abt one option that is to change the type of the field to varchar but i wanna use datetime and still wanna get rid of the the time in table. please help thanx n regards AnnuAnnu You can convert it like this...SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { DateTime d = (DateTime...

Replicating a datetime field from ASE 11.5 to date field on MVS DB2
We are using Rep Server 11.0.2 to replicate data from SYBASE ASE 11.5 to MVS DB2 using the MDI Gateway (yes we are a little behind the times). We have a datetime field on Sybase which is nullable which is defined as a date field on MVS DB2. On the insert funtion string, we insert with a dummy date like '1900-01-01' and turn around with an update using a DB2 function since DB2 allows functions on updates but not inserts. This works fine until we have a NULL value for that date. Has anyone encountered this problem? Thanks for any help! Deanna.Walton@anico.com ...

Microsoft JET Database Engine + Date/Time fields
DW.Net 2.0 Should I take a special consideration using Date/Time fields in Microsoft JET Database Engine? I get this error: "UpdateData Failed due to a Database error" with code -524553244 when I use date/time fields. If I change that field to numeric, the datawindow works well. Any idea? Maybe a regional setting? Thanks in advance. ...

Web resources about - Converting Date field and Time field to DateTime - embarcadero.delphi.database

Facebook Begins Converting Users To HTTPS
Are you willing to sacrifice a little bit of speed for a lot more safety? Facebook is asking that very question with its announcement that it ...

Facebook No Longer Converting Groups Into Pages
Back when Facebook first launched Facebook Pages, many businesses and brands who had built up substantial audiences in their Facebook Groups ...

Vert - simply converting for iPhone, iPad, and iPod touch on the iTunes App Store
Get Vert - simply converting on the App Store. See screenshots and ratings, and read customer reviews.

Converting SIM Card to Micro SIM Card - Flickr - Photo Sharing!
Place new Micro SIM into the iPhone SIM card tray

Ayaan Hirsi Ali on Converting Muslims to Christianity - YouTube
Complete video at: http://fora.tv/2010/07/29/Nomad_From_Islam_to_America_with_Ayaan_Hirsi_Ali Ayaan Hirsi Ali explains her support of missionary ...

Click go fears of converting print files
Is there a way to convert a print queue item to a .RTF or .PDF file? I like to save or email them. - The Sydney Morning Herald

Sudanese woman ordered to hang under sharia law for converting to Christianity gives birth
Khartoum, Sudan: A Christian Sudanese woman sentenced to hang for apostasy has given birth in jail, a Western diplomat said on Tuesday.

Imams warn against radicalism to Aboriginal inmates converting to Islam
The prison system has enlisted the help of ASIO to crack down on radicalisation behind bars amid revelations that Aboriginals are converting ...

Converting the world's companies one by one - The Science Show - ABC Radio National (Australian Broadcasting ...
Image: Trucks carrying logs make their way up a road in Jambi, Indonesia. A vast area of the Sumatran forest, and orangutan habitat, is being ...

Rothesay building new arena, converting existing rink to fieldhouse
The Town of Rothesay plans to build a new arena and convert the existing one into a fieldhouse.

Resources last updated: 1/14/2016 9:32:57 PM