0

I am using SSRS 2017 to query a SAP hana database using an ODBC connection.

I return a date column BUDAT as 20190101. I am trying to convert this to a date, but in the Sataset screen won't let me use the CONVERT or FORMAT command :

Attempt #1:

CONVERT(DATE, RIGHT(SAPABAP1.AFRU.BUDAT, 2) + SUBSTR(SAPABAP1.AFRU.BUDAT, 3, 2) + LEFT (SAPABAP1.AFRU.BUDAT, 4))

I get an error

Invalid or missing expression

when I "enter" on the code I put in.

Attempt #2

format(SAPABAP1.AFRU.BUDAT, "dd/MM/yyyy")

SQL Server accepts the syntax, but when the query tried to run, I get the error

General error;260 invalid column name;dd/MM/yyy:line1 col 1029 (as pos 1028)

I have spent the last few days off an on trying themes on this code but to no avail

I would like to see the output as dd/MM/yyyy.

Ole V.V.
  • 81,772
  • 15
  • 137
  • 161
Tony K
  • 1
  • 1
  • 4
  • You're using SAP Hana - not sql server. You need to use the appropriate sql features, functions, and syntax for SAP. If you are retrieving a date column, why do you attempt to convert it in the actual query? A better place to do that is where you display it. – SMor Jul 09 '19 at 16:29
  • Good morning thank you for the infromation, so in reality when I query SAP and pull the data over and its presented on the screen SQL converted the type of this field to NVARCHAR, I asumed that as I was in SQL I would then use SQL code to convert the data type in the field to what I wanted it to be ? do you have any idea on the sql/sap code to convert this field to a date ( note it works like a treat in Power BI just click on the row header and set it as a date and vloia its done ). – Tony K Jul 11 '19 at 08:20
  • Replace double quotes in your format statement with single ones (apostrophes). – Arvo Jul 11 '19 at 08:23
  • Hi Arvo, I tried that and also this does not work. – Tony K Jul 17 '19 at 11:38

2 Answers2

0

Once you consume DATS field in YYYYMMDD format, you will need to convert the String into the real Date value.

Assume SAPABAP1.AFRU.BUDAT is a valid string to be parsed:

For #1 (SSRS scenario), you need to use Format(Cdate(SAPABAP1.AFRU.BUDAT),"yyyyMMdd").

For #2 (SQL Server scenario), you need to use CONVERT(datetime,SAPABAP1.AFRU.BUDAT,112), where "112" refers to yyyyMMdd. You can also omit 112 as yyyyMMdd is ISO format. Alternatively, you can do CAST(SAPABAP1.AFRU.BUDAT as datetime).

AecorSoft
  • 414
  • 4
  • 10
0

Good morning, thank you for taking the time to answer.

2-CONVERT(datetime,SAPABAP1.AFRU.BUDAT,112)- when I enter the code in the sql side in the "column" where all the code goes, when I press enter to accept the code I get a microsoft visual studio error pop up window that says "function argument count error " and am unable to accept the code as you wrote.

2 - Copy and pasting CAST(SAPABAP1.AFRU.BUDAT as datetime) in - press enter I get the error invalid or missing expression.

What I have managed to do is get the date to resemble a date format using :

CONCAT(CONCAT(RIGHT (BUDAT, 2), SUBSTR(BUDAT, 5, 2)), LEFT (BUDAT, 4))

This gives the output from BUDAT 20190104 to 04012019 ( which is close to shat I need ).

Now 1-

Using the above code that works to get 01012019 I then used your code to convert this text to a date then format it ( note I changed the name of the field from Budat to postg_date for clarity ) :

=Format(Cdate(Fields!POSTG_DATE.Value),"ddMMyyyy")

The output in ssrs when its run gives me #ERROR in the output fieldI tried to include a screeen shot but I need 10 reutation points to do so :(

Currently we get the infromation I am looking at via an overnight download from SAP that goes in to a server that ises software called "universe" that is then squirted in to SQL that i Code against. Do you think SQL 2017 cannot get convert the information directly form SAP in a format it can read

Tony K
  • 1
  • 1
  • 4