DATEDIFF with SQL Server

Hello, 
 
I'm trying to convert SQL Server Management Studio Querty to Birt 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>count (
case
when datediff(hour, cast(substring(definst.edate,0,9) + ' ' + substring(definst.edate,9,2) + ':' + substring(definst.edate,11,2) as datetime), cast(substring(defhist.actdate,0,9) + ' ' + substringdefhist.actdate,9,2) + ':' + substring(defhist.actdate,11,2) as datetime)) > 24
then 'Yli24'
end)</pre>
 
I get error column hour not defined. 
 
I changed hour to 'hour' or 'h' or 'hh' I get no results from query. 
Any ideas how I could get this to work. The date is in annoying varchar 'yyyymmddhhmm'. 
I checked as much that the cast seems to work with substring.

Find more posts tagged with

Comments

K_L

Preview Results gives me following error if I use DATEDIFF as in this document http://www.birt-exchange.com/be/documentation/R11SP1/esd/help/docs/ioqb/ActuSQL-IOQB.3.46.html

A BIRT exception occurred.
Plug-in Provider:Eclipse.org
Plug-in Name:BIRT Data Engine
Plug-in ID:org.eclipse.birt.data
Version:2.6.0.v20100524
Error Code:odaconsumer.CannotGetResultSetMetaData
Error Message:Cannot get the result set metadata.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1: Invalid parameter 1 specified for datediff.
;
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid parameter 1 specified for datediff.

And as said earlier post I get error Unable to find column if I use the function as in Management Studio

Hans_vd

Hi K_L,

If you are on SQL Server, you definitely have to use the datediff function as in your first post.

When exactly does the error "column hour not defined" pop up?
Also, do you use an alias to name the column?
Do you already have a table on the report that has a column binding called "hour"?

If you can't get it to work, can you post the full query?

Regards
Hans

K_L

Hello and thanks for answering 
 
I don't use alias nor have hour in any other place. 
 
I get the error in SQL Select Query 
 
I stripped the query to basics 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT DATEDIFF(hour, '2007-05-07 09:53:01.0376635', '2007-05-07 09:53:01.0376635')
FROM DEFINST
WHERE DEFID = 17868</pre>
 
I attached a screenshot from the error. So you can see the error message. If I click ok or Run SQL it reverts to last working query. 
 
There is a similar post <a class='bbc_url' href='http://www.eclipse.org/forums/index.php?t=msg&goto=637187&S=787d622f8aa04ad2e1d640ef8a4e3151'>http://www.eclipse.org/forums/index.php?t=msg&goto=637187&S=787d622f8aa04ad2e1d640ef8a4e3151</a> but I'm too n00b to understand the answer. 
 
<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="78000" data-time="1307357699" data-date="06 June 2011 - 03:54 AM">
Hi K_L, 
 
If you are on SQL Server, you definitely have to use the datediff function as in your first post. 
 
When exactly does the error "column hour not defined" pop up? 
Also, do you use an alias to name the column? 
Do you already have a table on the report that has a column binding called "hour"? 
 
If you can't get it to work, can you post the full query? 
 
Regards 
Hans </blockquote>

Hans_vd

Hi K_L,

I have never seen that message before.
But can you try this:
- remove the comment from the query (or replace it by a comment in the format /* bla bla */ instead of -- bla bla). I have had some problems with "--" style comments in the past.
- Define an alias in the query so that BIRT has a column name to work with.

Regards
Hans

K_L

I removed the comment and it didn't help.
I tried to define alias, but was unable. How does one define alias for column?
I'm unable to use the columns tab under.

I made a Computed Columns with name hour, but that didn't help or I wasn't using it correctly. And simply putting like hour as "hour" has no meaning.

rihanna

<blockquote class='ipsBlockquote' data-author="'K_L'" data-cid="78035" data-time="1307383255" data-date="06 June 2011 - 11:00 AM">
I removed the comment and it didn't help. 
I tried to define alias, but was unable. How does one define alias for column? 
I'm unable to use the columns tab under. 
 
I made a Computed Columns with name hour, but that didn't help or I wasn't using it correctly. And simply putting like hour as "hour" has no meaning. </blockquote>
 
 
Have you tried with 'hour' or "hour"? Timestamp function description says: "Timestamp datediff(Varchar control, Timestamp start, Timestamp end). Returns the integer delta between the part of the two timestamps specified by the control string."