substring

bar_flyz

I just installed SSRS 2005 and I have experience with SQL.

How come this function does not work

SELECT SUBSTRING(YEAR_MONTH, 1, 2) AS Expr1
FROM table1

I get a message which states that this command is not supported by the provider

It works fine with other SQL tools like winsql

thanks

hunchback

Can you give us information about your Data Source

AMB

bar_flyz

what do you need

The DB is hosted by a third party and the field I am querying is a var char field.

I am baffled to why I am having issues with syntax

Sam Vella

what type of database are you querying sql server, oracle, informix etc..

What connection type and drivers are you using to connect.

I know WinSQL uses ODBC, however you could be using an OLE DB connection in SSRS, hence the slightly different behaviour.

bar_flyz

MS SQL server 2005

I am using the same ODBC connection I use for WINSQL and MS Acess

SQL server driver

Case does not work also.

TCP/IP

bar_flyz

any help is appreciated - see thread thanks

Shilpi Rachna

Can you give the exact error number and error message you get when trying to use SUBSTRING or CASE in a SELECT query

bar_flyz

During SQL check - I get "this command is not supported by the provider"

thanks

Sam Vella

Have you tried it without the substring (guessing its fine)

I'm also very confused about where the command is running and where the command is being executed.

Sounds like you are not querying a sql server database.

Have you asked the third party hosts for assistance

bar_flyz

yes, functions like left or right work and I can query data but some don't work like substring and if or case.

The third party is of NO help.

Could it be the ODBC

Manivannan.D.Sekaran

If LEFT & RIGHT are working fine - means you are connected with ACCESS database rather than SQL Server.

Try to use the MID function instead of SUBSTRING. Here the query..

Code Snippet

SELECT MID(YEAR_MONTH, 1, 2) AS Expr1

FROM table1

bar_flyz

that does nto work either

not a recognized function name

error: SQLNCCI.dll

Manivannan.D.Sekaran

Is the datatype is correct...

SELECT Substring(Cast(YEAR_MONTH as Varchar), 1, 2) AS Expr1

FROM table1

bar_flyz

field is varchar already. I did try and got the same error

thanks for trying

HELP!!

rusag2

YEAR_MONTH What is this function Does not exist in SQL.

I assume that you're trying to get a string that is formatted yyyymm from a date

Code Snippet

declare @dtInput datetime

set @dtInput = getdate()

select convert(varchar,year(@dtInput)) + right('00' + convert(varchar,Month(@dtInput)), 2)