Convert character field to Time

[Al Buchholz]

Re: Convert character field to Time

Just noticed something.

is

12:00:00:00 am

a legitimate time?

shouldn't it be pm?

or

00:00:00:00 am


Consider this:

dim myStart as Y
mystring = " "
myStart = ctot(mystring)
?mystart
= 12:00:00 00 am

?istime(mystring)
= .T.

?cShorttime(myStart)
= "000000000"

?

Interesting that values appears when the time is improperly formatted.... Maybe it's a sign of the times....

[Tom Cone Jr]

Re: Convert character field to Time

I don't think you can "blank" a short time field. The null field value is what you're getting [ Check help file topic "Null Table Field Values" ]

12:00:00 00 am

You can filter the table to exclude these values like this

CShortTime(your_field_name) <> "000000000"


You can filter the table to include these values like this

CShortTime(your_field_name) = "000000000"

[Al Buchholz]

Re: Convert character field to Time

I did find that opening a browse and pressing a spacebar while in the time_new field and saving the record blanked out the field.

So if you have an empty field, because it's new, then skip the time_new field in your code.

So then I guessed that a null_value() might work...

So see attached image of results and code.

[Tom Cone Jr]

Re: Convert character field to Time

Interesting and interesting. I got different results using the expression builder and the interactive window, while using vers 8 of Alpha Five.

[Al Buchholz]

Re: Convert character field to Time

Interesting and interesting. I got different results using the expression builder and the interactive window, while using vers 8 of Alpha Five.

Who has more fun than us????

[Mark Williams]

Re: Convert character field to Time

So take the comments off the if then and take out the istime() portion of the if.....

Ok, getting closer...

This script:

'Date Created: 25-May-2010 12:42:33 PM
'Last Updated: 25-May-2010 06:47:37 PM
'Created By  : Tim Sutherland
'Updated By  : Tim Sutherland
dim tbl as P
dim vcT as C 
'debug(1)	
	tbl = table.open("Sample")
	query.filter = ""
	query.order=""
	query.options=""
	tbl.query_create()
	
	tbl.fetch_first()
	while .NOT. tbl.fetch_eof()
		vcT = tbl.Time
		vct=stritran(vcT,"a"," am")
		vct=stritran(vcT,"p"," pm")
		if "0" = substr(vcT,1,1) then
		    vcT=substr(vcT,2,99)
		end if
if alltrim(vcT)>"" .and. istime(vcT)
	tbl.change_begin()
		tbl.Time_new = ctot(vcT)
		tbl.Time_stf=vcT
	tbl.change_end(.t.)
else
tbl.change_begin()
'		tbl.Time_New.blank_put()
		tbl.Time_stf=vcT
	tbl.change_end(.t.)
end if

	tbl.fetch_next()
	end while
	tbl.close()

gives these results:



Any idea why Time_New sees these values as invalid times but Time_Stf converts them correctly?
12:35P
11:40A
12:33P

[Al Buchholz]

Re: Convert character field to Time

Mark

Try the code in post 33.... I think it's exactly what you want. At least for this sample..

[Mark Williams]

Re: Convert character field to Time

Mark

Try the code in post 33.... I think it's exactly what you want. At least for this sample..

Bingo!

Thanks a bunch - appreciate the help!

[Tom Cone Jr]

Re: Convert character field to Time

Correction

It turns out I was wrong in post #32, above.

It is possible to assign null_value() to a shortTime field in your table and "blank" the value from that field.
-- tom