You can translate the question and the replies:

Transform text to data

HI everyone, I have a task to transform a text field to a timestamp field. The content of the initial field looks like this: "2017007", so it's like "YYYY0MM". I was trying aroudn with totimestamptz, but I don't know how to deal with this 0 in the middle between YYYY and MM. I thought before I will start splitting the string YYYY0MM into parts I will ask the community if there is an easier way to solve this. thank you! regards Masha
VQL
user
15-11-2022 09:56:07 -0500
code

4 Answers

Hi, The datetime pattern matcher parser struggles with extra numbers in a text to date string, so that '0' character between the year portion and the month portion of your string is causing some problems. Because of that, I think you are correct: I would use the VQL text functions to create a string of format 'yyyyMM' and then use TO\_TIMESTAMPZ on that string. To create the string, there are two options depending on what you're most comfortable with: either by using SUBSTRING to create substrings for the year and month portions, and then using CONCAT to create a single string or by using the REGEXP function to extract the year and month portions of the string. In either case, the string should then be in the format 'yyyyMM' which the TO\_TIMESTAMPZ function can handle. More information on the CONCAT, SUBSTRING, and REGEXP functions is available in the [Text Functions user manual](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/functions/text_functions/text_functions). Hope this helps!
Denodo Team
15-11-2022 19:08:58 -0500
code
This is awesome, thank you so much, it helped! Just to document the solution in case anyone will need something like this in the future: to_timestamp('yyyyMM', concat(substring(myfield, 0, 4), substring(myfield, 5, 7) )) Where my field is the initial formatting like 'yyyy0MM'.
user
16-11-2022 03:04:18 -0500
Hi, I have a related question. So I performed the operation and have now an additional field with the type localdate (I used to_localdate) after all, not to_timestamp, because I only care about the date). And now I am trying to use this field in WHERE clauses, which was the initial reason I even wanted to convert the field from text to date. And it is not working:( I already tried all possible syntaxis, with like, with =, with between, with using 00:00:00 or not using it... receiving the same error all the time. Do you have any ideas what can be wrong? My converted date field is fiscpertimestamp, the initial field is fiscper_0. If I just execute the view I can see that the field contents look like for example "2022-05-01". So I tried an expression: SELECT * FROM cldatabase.iv1uverrechnungsberichtmashatest WHERE fiscpertimestamp = '2022-05-01' And it (and all similar expressions) end up with the error below: Finished with error: Error executing query. Total time 1.5 seconds. QUERY [VIRTUAL] [ERROR] QUERY [JDBC WRAPPER] [ERROR] QUERY [JDBC ROUTE] [ERROR] Received exception with message 'SAP DBTech JDBC: [403]: internal error: Error opening the cursor for the remote database [SAP][ODBC Driver][SAP IQ]Procedure 'TO_TIMESTAMP' not found for query "SELECT SUM("/BIC/OUD2ADAUF"."/BIC/D1KARTZE"), ("/BIC/OUD2ADAUF"."CALDAY"), ("/BIC/OUD2ADAUF"."FISCPER"), ("/BIC/OUD2ADAUF"."FISCVARNT"), ("/BIC/OUD2ADAUF"."/BIC/A1MBRANC2"), ("/BIC/OUD2ADAUF"."CO_AREA"), ("/BIC/OUD2ADAUF"."/BIC/B1MELKAL"), ("/BIC/OUD2ADAUF"."/BIC/D1MOLAGST") FROM "sapbw_nls"."/BIC/OUD2ADAUF" "/BIC/OUD2ADAUF" WHERE CONVERT(DATE, TO_TIMESTAMP((SUBSTRING(("/BIC/OUD2ADAUF"."FISCPER"),CONVERT(INT,( CONVERT(DECIMAL,?) + 1) ),CONVERT(INT,( CONVERT(DECIMAL,?) - CONVERT(DECIMAL,?) ) )))+(SUBSTRING(("/BIC/OUD2ADAUF"."FISCPER"),CONVERT(INT,( CONVERT(DECIMAL,?) + 1) ),CONVERT(INT,( CONVERT(DECIMAL,?) - CONVERT(DECIMAL,?) ) ))), ? )) = ? GROUP BY "/BIC/OUD2ADAUF"."CALDAY","/BIC/OUD2ADAUF"."FISCPER","/BIC/OUD2ADAUF"."FISCVARNT","/BIC/OUD2ADAUF"."/BIC/A1MBRANC2","/BIC/OUD2ADAUF"."CO_AREA","/BIC/OUD2ADAUF"."/BIC/B1MELKAL","/BIC/OUD2ADAUF"."/BIC/D1MOLAGST" "'
user
16-11-2022 07:19:54 -0500
Hi, From the error that you posted, it looks like part of the query is being pushed down from Virtual Data Port to the data source. There are a lot of potential issues that could be causing this error here. If you have a valid support user, I would recommend submitting a support case so that the support team can investigate the logs and trace. Hope this helps!
Denodo Team
18-11-2022 18:12:36 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here