character to date field conversion

[FreeBird]

I have some character fields that are in this format 20100612 and I need to change them to date field...how do i do this?
i wasn't able to find it in help or the message boards.
If I just change it in the table restructure, data gets lost.
Please help someone. :)

[Tim Kiebert]

Re: character to date field conversion

Where and how did you look in the help? It is definitely in there.

[Tom Cone Jr]

Re: character to date field conversion

Have you discovered the date and time functions in the help system? The STOD() function seems promising.

[FreeBird]

Re: character to date field conversion

Thanks Tom, perfect. I was looking for date conversion and for some reason it wasn't coming up.

[FreeBird]

Re: character to date field conversion

Ok...here is a super newbie stupid question....where do I write this function to get this to work. Do I create a new date field and then there?

[Tom Cone Jr]

Re: character to date field conversion

Time to get acquainted with the user's guide. The chapter on Batch Operations has a section on "Update Operations". You can solve your problem by adding a date type field to your table and then defining an Update Operation that populates it from the current character field, using the stod() function.

Batch operations can create havoc in the hands of beginners and experienced veterans alike. My personal rule is to make two separate, good, backups, before running untested operations.

In fact, it's often simplest to work with a copy of the database instead of the live (actual) data while doing your design.

[FreeBird]

Re: character to date field conversion

Yeah I tried that. I went to operations, update, then selected my date field (which is not char), then I typed in:
stod() and it tells me invalid or incomplete expression.
I'm doing something wrong.

[FreeBird]

Re: character to date field conversion

I found out the hard way re: backups....you are totally right!!!

[Tim Kiebert]

Re: character to date field conversion

Ok, since you say you have c++ and vb experience we are not going to cut you as much slack. :) Just kidding.

STOD() takes a character parameter in the form of "YYYYMMDD" which you have in your original field.
So you should have something like stod(FieldName).
Replace 'Fieldname' with the name of the field that has the character data.

[FreeBird]

Re: character to date field conversion

Ok, I go to update operation, choose my table, then go to create operation, choose date field, in expression, I type STOD(char_field).
I get an error message that says expression does not evaluate to a date value.
What am I doing wrong?

[Tim Kiebert]

Re: character to date field conversion

Can you supply a sample of your app?

Is char_field the name of your field?
What is the size of that field? If it is larger than 8 try stod(alltrim(char_field))
Is the data in the form of YYYYMMDD

You will need to make sure that each entry is exactly of that form.

[FreeBird]

Re: character to date field conversion

Yes, char_field is the name of my field. The date and char field are the same size.
Here is the code. it will run the operation without error messages but date is not changed in the date field, it's jsut empty.

'Operation is based on current filter and order of current table.
'Get the current table's filter and order expression

dim mru as p
mru = mru_query("il")
current_filter = mru.filter
current_order = mru.order
current_flags = mru.flags

'Check to see if current filter/order is valid in the context of "il"
if current_filter <> "" then
if eval_valid(current_filter,"il") =.f. then
a5_Operation_Warning() 'Display warning if current selection is meaningless
current_filter = ".t."
end if
end if

if current_order <> "" then
if eval_valid(current_order,"il") = .f. then
current_order = ""
end if
end if

a_tbl = table.open("il")
ON ERROR GOTO ERROR1306201000105403


DIM a5_operation_filter as C
a5_operation_filter = current_filter
query.filter = a5_operation_filter
DIM a5_operation_order as C
a5_operation_order = current_order
query.order = a5_operation_order
query.options = "I"+current_flags
query.description = "Temporary Query"
a_tbl.query_create("N")

update.fields = 2
update.field1 = "LIC_ISSUED"
update.expr1 = "CTOD(LIC_F_ISS)"
update.field2 = "LIC_EXP_DATE"
update.expr2 = "CTOD(LIC_EXP)"

a_tbl.update()


GOTO CONTINUE1306201000105403
ERROR1306201000105403:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Update Operation"+crlf()+error_text_get())
END
CONTINUE1306201000105403:
a_tbl.close()'If the Operation is run from within a Form or Browse, then refresh the window
if is_object(topparent.this) then
if topparent.Class() = "form" .or. topparent.class() = "browse" then
topparent.Refresh_layout()
end if
end if

[FreeBird]

Re: character to date field conversion

sorry, I meant to say my character field is a character. The names are in the code.

[FreeBird]

Re: character to date field conversion

In looking at this code, how does it know to takenad populate the date fields, it will cahnge them but there is no command to populate it...is that what CTOD does? it changes it and then it populates it too?

[Tim Kiebert]

Re: character to date field conversion

In looking at this code, how does it know to takenad populate the date fields, it will cahnge them but there is no command to populate it...is that what CTOD does? it changes it and then it populates it too?

No. It is the inner workings of the update operation. It takes the result of update.expr1 and assigns it to the field specified in update.field1.


I am a bit confused as I thought we were using STOD().