Converting TIME to DATE, or other way around

[preston2]

On a form I have a variable that has a control type of "Radio Button". The radio buttons select the choices and assign a number value to the variable.

In a script I am looking at the variables numeric value and then assigning other variables values to a query search. This is almost exactly like the example in Alpha Sport.

Two of the options assign date values that are queried against a "Date" filed in the table. This works like a champ.

The problem I am having is I also want to be able to query a date against a "Time" field, just looking at the "Date" part of the time field. If that makes sense.

In the table I have Date field, Date,D,8,0 that contains the date an invoice was created.

Then there is a Time field, tmsart,T,17,0 that contains the date and time the job is scheduled to be printed. In the query I need to be able to only search against the date part of the time field.

Here is my code.

if Options = 1 then 'Show invoices by Date
	filter =  "date = [varD->today]"
else if Options = 2 then 'Show invoices by range of dates
	filter = "between(date,[varD->begindate],[varD->enddate])"
else if Options = 3 then 'Show invoices by schedule date
	filter =  "tmstart = [varD->today]"	
else if Options = 4 then 'Show invoices by range of scheduled dates
	filter = "between(tmstart,[varD->begindate],[varD->enddate])"	
else if Options = 5 then 'Show All invoices
	topparent.queryrun("invoice = .t.","tmstart","","No","Wo_Header",.f.)		
	goto DONE
end if 

dim typefilter as c
if Options2 = 1 then
 typefilter = "invoice=.T..AND.exported=.T..AND.export_it=.T."
 else if Options2 = 2 then
 typefilter = "invoice=.T..AND.export_it=.T..AND.EXPORTED=.F."
 else if Options2 = 3 then
 typefilter = "invoice=.T."
 else if Options2 = 4 then
 typefilter = "invoice=.T..AND.exported=.F..AND.EXPORT_IT=.F."
 end if
typefilter = filter+".AND."+typefilter 
 
'substitute the parameters in the filter expression
typefilter = replace_parameters(showfilter,local_variables())

topparent.queryrun(typefilter,"tmstart","","no","Wo_Header",.T.)'Run query

DONE:

Thanks..

[Stan Mathews]

Re: Converting TIME to DATE, or other way around

? time("MM/dd/yyyy",now())
= "09/01/2015"
? time("MM/dd/yyyy",now()) = dtoc(date())
= .T.

Substitute your time field for now() and your date field for date().

[preston2]

Re: Converting TIME to DATE, or other way around

filter = "time("MM/dd/yyyy",Tmstart) = [varD->today]" throws errors as I assume there are quotation marks within quotation marks.

What I may need to do is convert the time field to a date field in the table and the query against that.

[Stan Mathews]

Re: Converting TIME to DATE, or other way around

It throws errors because you have quotes within quotes.

filter = "time(\"MM/dd/yyyy\",Tmstart) = dtoc([varD->today])"

If I'm reading it right.


Note:

filter = "time("MM/dd/yyyy",Tmstart)

start reading from the first double quote. When you come to the second, the string ends (for Alpha) unless you escape it.

[preston2]

Re: Converting TIME to DATE, or other way around

Ok better but no cigar yet.

When replace_parameters(showfilter,local_variables()) is run the results are "tmstart = {09/01/2015}.AND.invoice=.T..AND.export_it=.T..AND.EXPORTED=.F." which will not work in the query.

[Stan Mathews]

Re: Converting TIME to DATE, or other way around

Don't see that here.

dim today as D
today = date()

filter = "time(\"MM/dd/yyyy\",Tmstart) = dtoc([varD->today])"

? replace_parameters(filter,local_variables())
= time("MM/dd/yyyy",Tmstart) = dtoc({09/01/2015})

tmstart = now()
? time("MM/dd/yyyy",Tmstart) = dtoc({09/01/2015})
= .T.

[preston2]

Re: Converting TIME to DATE, or other way around

Here is what I think is the problem.

The filter = "time(\"MM/dd/yyyy\",Tmstart) = dtoc([varD->today])" converts the Time format to a character Date format which when queried against a Time field will always not find any matching results because the Time field in the table still contains the hours/minutes and seconds.

So I have added two fields to the table to try to convert the tmstart field into a date only field that I can then query against. I have gotten it to convert to a character format using CDATE(TMSTART) which results in something like "20150901" which will never match anything in the [varD->today] option variable.

Trying to convert the field value of "20150901" back into a date using CTOD() results in nul.

Still trying things.

[Stan Mathews]

Re: Converting TIME to DATE, or other way around

converts the Time format to a character Date format which when queried against a Time field

time(\"MM/dd/yyyy\",Tmstart) converts the time field (tmstart) to a character date value and dtoc([varD->today]) converts a date value to a character date value. The filter = "time(\"MM/dd/yyyy\",Tmstart) = dtoc([varD->today])" should compare those character values.

? ctod("20150901")
= { / / }

? stod("20150901")
= {09/01/2015}

[preston2]

Re: Converting TIME to DATE, or other way around

I already tried the STOD() in a calculated field rule and it says it is a invalid or incomplete expression

the Field Sch_datec has CDATE(TMSTART) = 20150901

The field Sch_date will not accept STOD(SCH_DATEC)

[Stan Mathews]

Re: Converting TIME to DATE, or other way around

STOD() fails if the SCH_DATEC is ever blank or invalid.

http://www.alphasoftware.com/alphafo...or-*if_error()

In such cases you can use

*if_error(STOD(SCH_DATEC) ,{})

[preston2]

Re: Converting TIME to DATE, or other way around

I get a "Cannot use function in this context".

The SCH_DATEC field is not blank so it cannot be what is causing the "invalid or incomplete expression"

If I take the actual value of Sch_datec and use it like STOD("20150901") it works but that does not do me any good as the value changes.

I may be wrong but the way I see it is the only way to run a query using just the date part of a time field is to have that value stored as a date in a table field and so far I have not found a way to do that.

I may just have to change the options variable to use a time field to query against a time field but that complicates things for the end user that only want to see scheduled items for a date or date range and could care less about the time of day it is scheduled.

[Stan Mathews]

Re: Converting TIME to DATE, or other way around

Time to attach a sample table.

[preston2]

Re: Converting TIME to DATE, or other way around

Time to attach a sample table.

I am going to try and do the conversion in a script instead of using table field rules. If that does not work I will create a sample table and form.

[G Gabriel]

Re: Converting TIME to DATE, or other way around

Not sure what is the variable you are using in the filter to test against the time filed.
Assuming it's "today"
Let's call it v_today instead.
For a starter, it has to be dimmed as shared at minimum if not global depending on where you are running this script from
So, your script will look like this:

dim global v_today as d
filter="ctod(dtoc(Tmstart))=var->v_today"

[preston2]

Re: Converting TIME to DATE, or other way around

Looks like I may have it working in a script.

I found that after I got the character date value from the tmstart field into the sch_datec field I needed to do an alltrim on it.

What I have working is being run in a script when they set the scheduling start time. It now takes the date part of the start time and stores the date part in a date field that can be used for the query.

Now to do the date range code and make sure it works and then it looks like I have it. I am not even going to try and see if I can make it work with field rules and the alltrim as I seem to be on the right track doing it in a script.


As always, thank you very much for your assistance Stan.