cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
Botzal
Level III

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

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

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. 

View solution in original post

2 REPLIES 2
pmroz
Super User

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

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. 

Mauro_Gerber
Level IV

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

For datetime I use the followig statement:

 

t_now = "'" || Substitute(Format(today(),"yyyy-mm-ddThh:mm:ss"),"T"," ") || "'";
"I thought about our dilemma, and I came up with a solution that I honestly think works out best for one of both of us"
- GLaDOS