You can translate the question and the replies:

calculate years between string to now?

Hi I have tried several ways to solve my problem. I have a field with a number, stored as text. The first 8 figures represent YYYYMMDD, and then there are five positions that I dont want to use. For exampel: 19010210-abcd. I wold like to calute how many ears there is between 19010210 and today. I have tried so many ways. For exampel: concat(substring(iv_slutenvård_vst_pågående.personnummer, 0, 4), '-', substring(iv_slutenvård_vst_pågående.personnummer, 4, 6), '-', substring(iv_slutenvård_vst_pågående.personnummer, 6, 8)) This gives me 1901-02-10. Then I tried to convert to date. But the result is "'Conversion failed when converting date and/or time from character string.'" I have tried to_date, formatdate, cast... you name it. Allso tried substring(iv_slutenvård_vst_pågående.personnummer, 0, 8 that result in 19010210. And then tried to convert to date. Does anyone have a sollution to this?
user
29-05-2023 05:32:06 -0400
code

1 Answer

Hi, I’d try to use the function [TO_DATE](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/functions/datetime_functions/datetime_functions#to-date) and the function [SUBSTRING](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/functions/text_functions/text_functions#substring-substr) as in the following example: `to_date('yyyyMMdd', substring('19010210-abcd', 0, 8) )` The [SUBSTRING](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/functions/text_functions/text_functions#substring-substr) function allows you to extract the substring until the eighth character. The [TO_DATE](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/functions/datetime_functions/datetime_functions#to-date) function allows you convert the text value obtaining a datetime in a specific format, into a value of type date that represents this date. For more information, refer to the sections [Text Functions](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/functions/text_functions/text_functions#substring-substr) and [Datetime Functions](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/functions/datetime_functions/datetime_functions#to-date) of the Virtual DataPort VQL Guide. Hope this helps!
Denodo Team
01-06-2023 07:46:19 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here