DATEPART function error

Discussion forum about all things Report Builder (all versions).
edwhitenc
United States of America
Posts: 3
Joined: 27 Jun 2019, 13:13
Location: USA

DATEPART function error

Post by edwhitenc » 11 Jul 2011, 22:48

I am trying to use Gupta Report Builder 5.2 to connect to a SQL Server database to generate a standalone report for a customer.

I have successfully connected to the database and can generate some reports; however, when I try to use the DATEPART function in a formula in the query it will not recognize the parameter for the keyword. I have tried the following syntax:

DATEPART(w, <date>) where <date> is a date column in the query

DATEPART(‘w’,<date>)

In the first case I get an error message which indicates an unrecognized variable.

In the second I get an error message which indicates an invalid keyword. I have tried all the possible abbreviations for week with the same result.

I am familiar with the DATEPART function in SQL Server and have been successful using it previously. Can you shed any light on why this is not working for me?

Jeff Luther

Re: DATEPART function error

Post by Jeff Luther » 12 Jul 2011, 23:51

Well, I just tried this using SQLTalk and a sample table with a date:

select c1_int, c2_datetime, datepart( w, c2_datetime ) from <table>

and got this back:

Code: Select all

C1_INT        C2_DATETIME
=========== ==================== ===========
     1      12-DEC-2012 00:00:00     4
so... it works if the select is sent directly to MS SS. I suspect that when you put DATEPART in a ReportBuilder formula it expects that to be a RB function, as shown in the right column of the formula editor. If so, likely that's why it isn't working. RB doesn't know DATEPART.

Unless you are doing something otherwise, you might create a VIEW of the query you want and have RB query that. If I'm wrong in my guess as to what you're doing, let us know more in detail how you are trying to create the formula.

edwhitenc
United States of America
Posts: 3
Joined: 27 Jun 2019, 13:13
Location: USA

Re: DATEPART function error

Post by edwhitenc » 13 Jul 2011, 06:09

Jeff,

I understand what you are saying; however, the DATEPART function is showing in the rignt hand side of the RB formula editor screen (along with all the usual SQL Server functions). I am doing this in a report builder query connected to a SQL Server database using sqlodb32.dll (in the sql.ini) and an ODBC DSN connecting to the database with the SQL Server driver.

Other functions which don't require a Keyword work correctly. The keyword is not being recognized.

If the RB query formulas are supposed to use RB functions then why are they not showing in the formula editor window?

Thanks,

Ed

edwhitenc
United States of America
Posts: 3
Joined: 27 Jun 2019, 13:13
Location: USA

Re: DATEPART function error

Post by edwhitenc » 13 Jul 2011, 06:28

Jeff,

Further info:

sql.ini file:

[odbcrtr]
longbuffer=32000
autocommit=off
odbctrace off=off
odbctracefile=sql.log

A Gupta.ini file was created automatically somehow which specifies the DSN name and it's relationship to the database being used. The connection works. The only thing that does not work is the passing of the Keyword parameter.

Ed

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests