Choose Language Hide Translation Bar
Highlighted
Botzal
Community Trekker

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

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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

0 Kudos
2 REPLIES 2
Highlighted
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

0 Kudos
Highlighted
Mauro_Gerber
Occasional Contributor

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"," ") || "'";
0 Kudos