Open SQL database - user input (Dates) - Error converting data type varchar to datetime.

[#1]

Hi,

I'm trying to connect to a SQL database in a script using user input for dates.

When I run the script I get the following error - 

"[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to datetime."

Please, how can I solve this problem?

Thx

nw = New Window( "test",
	H LIST BOX(
				       Text box("Start Date"),
				       a = numbereditbox(0),
                       a << set format(format("m/d/y")),
                       a << set width(12),
                       a << set(today()),				     
				       Spacer Box( size( 10, 20 ) ),
				       Text box("End Date"),
				        b =  numbereditbox(0),
                       b << set format(format("m/d/y")),
                       b << set width(12),
                       b << set(today())),
				       Spacer Box( size( 1, 20 ) ),    

	Button Box( "OK",
		farray = Eval List(
			{a << get text, b << get text}
		);
		nw << close window;
	)
);

dta= Open database("DRIVER=SQL Server; SERVER=XXX;  Trusted_connection = Yes",
"
EXEC dbo.listData @Criteria = '
<systemE>
  <Data Name=\!"Alarm\!">
    <Columns>
     <Column Name=\!"Timestamp\!"/>  
    </Columns>
    <Where>
     <Column Name=\!"Resource\!" Operator=\!"Like\!" Value=\!"%a12%\!"/> 
    </Where>
    <Projects>
      <Project Name=\!"abc\!"/>
    </Projects>
  </Data>
</systemE>'
,@StartDateTime = '^farray[1]^'
,@EndDateTime   =' ^farray[2]^'");

[#2 · Accepted solution]

I changed your window to be modal.  Otherwise the window displays and the SQL is attempted to be run all at once.  I'm not sure of the format needed for dates in SQL Server, so you may need to do a little research on that.  Copy the SQL statement that outputs to the log and try running it manually in a SQL server window.

nw = New Window( "test", <<modal(),
	H List Box(
		Text Box( "Start Date" ),
		a = Number Edit Box( 0 ),
		a << set format( Format( "m/d/y" ) ),
		a << set width( 12 ),
		a << set( Today() ),
		Spacer Box( size( 10, 20 ) ),
		Text Box( "End Date" ),
		b = Number Edit Box( 0 ),
		b << set format( Format( "m/d/y" ) ),
		b << set width( 12 ),
		b << set( Today() )
	),
	Spacer Box( size( 1, 20 ) ),    

	Button Box( "OK",
		start_date = Format( a << get, "m/d/y" );
		end_date   = Format( b << get, "m/d/y" );
	)
);

sql_statement = evalinsert("\[
EXEC dbo.listData @Criteria = '
<systemE>
  <Data Name="Alarm">
    <Columns>
     <Column Name="Timestamp"/>  
    </Columns>
    <Where>
     <Column Name="Resource" Operator="Like" Value="%a12%"/> 
    </Where>
    <Projects>
      <Project Name="abc"/>
    </Projects>
  </Data>
</systemE>'
,@StartDateTime = '^start_date^'
,@EndDateTime   = '^end_date^']\");

show(sql_statement);
// Uncomment the next line to run the command in SQL Server
//dta= Open database("DRIVER=SQL Server; SERVER=XXX;  Trusted_connection = Yes", sql_statement);

Other changes - if you want to use the ^xxx^ construct you need to enclose that in evalinsert().  I used the quoting escape characters \[ and ]\ so your SQL statement is a bit cleaner. 

[#3]

For datetime I use the followig statement:

t_now = "'" || Substitute(Format(today(),"yyyy-mm-ddThh:mm:ss"),"T"," ") || "'";