Get date into Args?

[Keith Hubert]

Hi Guys,

I am using the following code to open a dialog to get two dates.

DIM SHARED vd_first_date as D
DIM SHARED vd_last_date as D
DIM SHARED varC_result as C
ok_button_label = "&OK"
cancel_button_label = "&Cancel"
varC_result = ui_dlg_box("Date Range Selector",<<%dlg%
{region}
Start Date:| [%DATE;P=popup.calendar(dtoc(vd_first_date));I=popup.calendar%.40vd_first_date!vd_first_date_*];
Last Date:| [%DATE;P=popup.calendar(dtoc(vd_last_date));I=popup.calendar%.40vd_last_date!vd_last_date_*];
{endregion};
{line=1,0};
{region}
<*15=ok_button_label!OK> <15=cancel_button_label!CANCEL>
{endregion};
%dlg%,<<%code%
if left(a_dlg_button,14) = "vd_first_date_" then 
	if a_dlg_button = "vd_first_date_killfocus" then 
		vd_first_date = ctod(dtoc(vd_first_date))
	end if 
	a_dlg_button = ""
end if 

if left(a_dlg_button,13) = "vd_last_date_" then 
	if a_dlg_button = "vd_last_date_killfocus" then 
		vd_last_date = ctod(dtoc(vd_last_date))
	end if 
	a_dlg_button = ""
end if
%code%)

I need to use the dates as values to filter an SQL table in a date range. How do I convert the Xbasic Dates to SQL syntax?

It seems the SQL must be without double quotes, which is what you get when converting with DTOC(vd_first_date,"1/")

Is there a function to substitute the double quotes for single quotes?

eg WHERE APPLICATION_DATE BETWEEN :vd_first_date AND :vd_last_date

[christappan]

Re: Get date into Agrs?

I think this is posted in the wrong forum, but I know I'm able to set dates to date_format(date(),"yyyy-MM-dd") or time("yyyy-MM-dd",date()) and set the argument to that.

[Keith Hubert]

Re: Get date into Agrs?

Hi Chris,

Thanks for you suggestion.

In interactive I get:
x=date_format(date(),"yyyy-MM-dd")
?x
= {30/07/2013}

SQL does not like the curly brackets, any suggestions how to end up with '30/07/2013' ?

[christappan]

Re: Get date into Agrs?

I don't know how you're ending up with that, but x is going to be a character string, that the args.set("arg1",x) then takes care of.

[Stan Mathews]

Re: Get date into Agrs?

Hi Chris,

Thanks for you suggestion.

In interactive I get:
x=date_format(date(),"yyyy-MM-dd")
?x
= {30/07/2013}

SQL does not like the curly brackets, any suggestions how to end up with '30/07/2013' ?

Try that again, Keith. I get

x=date_format(date(),"yyyy-MM-dd")
? x
= "2013-07-30"

You might want to begin with

delete x
dim x as C
x=date_format(date(),"yyyy-MM-dd")
? x
= "2013-07-30"

From your original request it looks like you really want

x=date_format(date(),"dd/MM/yyyy")

Footnote/Warning: Due to Alpha's automatic type conversion you can

delete x
dim x as D
x=date_format(date(),"yyyy-MM-dd")
? x
= {07/30/2013}

with the result that Alpha converts the character result of date_format() "invisibly".

[madtowng]

Re: Get date into Args?

Time works nice and easy.

? time("yyyy-MM-dd",date())
= "2013-07-30"
? time("yyyy-MM-dd",now())
= "2013-07-30"

? time("yyyy-MM-dd",{12/31/2012})
= "2012-12-31"

[Keith Hubert]

Re: Get date into Args?

Hi Guys,

Thanks for your input.

My problem is not getting the date format mm/dd/yyyy, but getting it into a format that can be used as part of the filter in a SQL WHERE statement uses ARGS.

Maybe I'm missing something here about setting an ARG variable with a date.

[glenschild]

Re: Get date into Args?

Hi Keith

Try

args.set("vd_first_date",convert_type("vd_first_date","D"))

Make sure you have declared args

[madtowng]

Re: Get date into Args?

The SQL_DATE() function generates a character string with the format: {YYYY-MM-DD}.

Supported By

Alpha Five Version 8 and Above

Example

? sql_date(date() )
= "{2006-03-22}"? sql_date(date() )
= "{2006-03-22}"

[Keith Hubert]

Re: Get date into Args?

Hi Guys,

Many thanks for all you help.

My mistake was trying to work from what my customer had already started instead of using the built in Alpha SQL genie.

Using the SQL Genie in AS did the trick.

[christappan]

Re: Get date into Args?

Gregg, don't know how I'd missed that but thanks for the help. That's a great function to have. I am curious if anyone knows what the second option does. The first option changes from date type to character but I don't see any difference in the second option....

?sql_date(date(),1)
= "{2013-07-31}"
?sql_date(date(),2)
= "2013-07-31"

?sql_date(now(),1)
= "{2013-07-31 10:39:47.939}"
?sql_date(now(),2)
= "2013-07-31 10:39:56 104000"

[jacobe]

Re: Get date into Args?

I think it corresponds to MS SQL datetime and datetime2 formats.

datetime2 has 100 nanosecond accuracy.

[madtowng]

Re: Get date into Args?

I tried using the sql_date() function.
Unfortunately, it includes the {}.
I went back to the time() function, which formats the date correctly.

[christappan]

Re: Get date into Args?

If you just want the date portion, see this above:

?sql_date(date(),1)
= "{2013-07-31}"
?sql_date(date(),2)
= "2013-07-31"

That said, I tried using it on a time yesterday and couldn't seem to make it MySQL-happy, so I'm still trying it out.

[madtowng]

Re: Get date into Args?

Part of why I like the time() function much better.

? time("yyyy-MM-dd 0h:0m:0s",now())
= "2013-08-03 11:40:39"