XBASIC: SQL Argument for Date Range

[pettechservices]

Hi,

I am trying to use args.set(argname, "blahblah") for some SQL work but I am not sure how I can use it to specify a date range.

I want to be able to say args.set("startdate", "between 10-10-2020 and 10-20-2020") <-- just an example ... I know it isn't correct.

Any tips? I checked documentation and searched forum but I'm not able to find anything related to this.

[drgarytraub]

args.set("vdate1","2020-10-10")
args.set("vdate2".'2020-10-20"')

and then in where clause:

WHERE date BETWEEN :vdate1 AND :vdate2

[pettechservices]

Hi Doc, thanks! I feel so stupid - I'm not sure where my mind was when asking that.

[drgarytraub]

Pete,

No worries. I am sure we have all done that sort of thing. I know I have many times. Happy to be of help.

[pettechservices]

Hey Doc, I have a follow up question...been stuck on this for a few days now and just lost.

I have a form with some fields, lets say "nameFirst", "nameLast", and "State". The user can click a button and I am trying to generate a SELECT statement in XBASIC.

i need to create a WHERE clause to include one or more of the above fields, ie SELECT * from table WHERE nameFirst = 'Chris' and nameLast = 'Doe'

Or if they enter just a last name and state could be "....WHERE nameLast = 'Doe' and state = 'NJ'"

On my actual form, there are numerous other fields. Any tips on how to generate the where clause to include or not include filled in fields? I did not want to do a ton of IF/THEN statements because then I need to add "and" between them etc.

This is on a UX.

Thanks

[Al Buchholz]

I approach these types of things with a select case statement.

You can do this in 2 stages.

Stage 1:
cmd="SELECT * from table WHERE"
add_and=.f.

select
case nameFirst > ""
cmd=cmd+"nameFirst = "+ s_quote(nameFirst)
add_and = .t.

case nameLast > ""
if add_and then
cmd=cmd+" .and. "
end if
cmd=cmd+"nameLast = "+ s_quote(nameLast)
add_and = .t.

case State > ""
if add_and then
cmd=cmd+" .and. "
end if
cmd=cmd+"State = "+ s_quote(State)
add_and = .t.


end select

Stage 2:

From a list of fieldnames that you are going to select from, you can loop through them and build the code shown in stage 1.

Then use evaluate_template() to execute that code.

[pettechservices]

Thanks Al! I'll give this a shot. I appreciate it!

[madtowng]

and just when you think all hope is gone, another option is provided.
I think this does what you want. You can use the supplied UX to test the code, there is nothing really special.
I did not include a lot of validating, just a basic proof of concept type thing.
Let me know if you have any questions.

dim myfilter as c
dim tracker as n = 0
dim myArgs as sql::Arguments

if *count(e.dirtyColumns.R1) > 1 then
for each choice in e.dirtyColumns.R1
tracker = tracker + 1
if len(myfilter)>0 then
myfilter = myfilter + " and " +choice.value+" = "
myArgs.Set("fld"+remspecial(str(tracker)),eval("e.datasubmitted."+choice.value))
myfilter = myfilter+":fld"+remspecial(str(tracker))
else
myfilter = "where " + choice.value + " = :fld1"
myArgs.Set("fld"+remspecial(str(tracker)),eval("e.datasubmitted."+choice.value))
end if
next

else
myfilter = "where " + e.dirtyColumns.R1 + " = :fld1"
myArgs.Set("fld1",eval("e.datasubmitted."+e.dirtycolumns.r1))
end if

[pettechservices]

Thank you!! Sorry did not realize you answered this also. THANK YOU

[madtowng]

I can't be the only one that gets wrapped up in work stuff and doesn't have time to check the message board.
Did it work for you?

[pettechservices]

yes, thank you! I used a combination of yours and Al's! I have been able to use these routines in multiple places - making life so much easier lol Thank you!

[Jay Talbott]

Gary's answer is absolutely correct.

I do want to point out that it is not always possible to hard-code the date(s) for a SQL expression, and if you need to use a date variable, Alpha provides the ability to turn that into a SQL correct date with the function: format_data() - note that the function is format_DATA, not format_DATE
From the interactive window.

a = {01/01/2021}

?a
= {01/01/2020}

?format_data(a,"12-")
= "2020-01-01"

There are other ways to bring dates into SQL format, but this is a very concise way to do it.

Hope this helps.
Jay

[pettechservices]

Ok, thank you! We do convert dates to SQL format but I think using a different method - so this is good to know! Thanks!