topic Re: Open SQL database - user input (Dates) - Error converting data type varchar to datetime. in Discussions

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

Botzal — Sun, 05 Jan 2020 18:21:03 GMT

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]^'");

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

pmroz — Mon, 06 Jan 2020 16:37:08 GMT

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.

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

Mauro_Gerber — Tue, 07 Jan 2020 07:30:02 GMT

For datetime I use the followig statement:

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