Date -12 digit integer to a date conversion

Question & Answer
, , ,
1

2022090920221114 i want this integer no to convet it into date like-Sep. 9 ’22 - Nov. 14 ’22

2

Hello Siddhi,

I guess the expected output of number you want is going to be string anyway since i dont know any such date format.

The number consist of 2 dates ( lets say start date - end date)

you may need to convert number to string into 2 parts and then concat the two fields … may be that way you would be able to get your expected fields but again as String.

Hope this gives some idea.

Cheers,
Deep

3

Hi @Siddhi_21

Please try the following calculated field and modify it according to your requirements.

Example: (Syntax may vary) - Replace the date text with the relevant field.

concat(
    formatDate(parseDate(concat(
        substring('2022090920221114', 1, 4), '-',
        substring('2022090920221114', 5, 2), '-',
        substring('2022090920221114', 7, 2)
    ), 'yyyy-MM-dd'), 'MMM/dd/yyyy'),
    ' - ',
    formatDate(parseDate(concat(
        substring('2022090920221114', 9, 4), '-',
        substring('2022090920221114', 13, 2), '-',
        substring('2022090920221114', 15, 2)
    ), 'yyyy-MM-dd'), 'MMM/dd/yyyy')
)

Please refer the below date format documentation this might be helpful for you.

1 Like
4

i want to create a calculate field for a column in which data type is string not for particular value…will it work for whole column?

5

Hi @Siddhi_21

Replace the value with the relevant field from your dataset.

concat(
    formatDate(parseDate(concat(
        substring({Your Field}, 1, 4), '-',
        substring({Your Field}, 5, 2), '-',
        substring({Your Field}, 7, 2)
    ), 'yyyy-MM-dd'), 'MMM/dd/yyyy'),
    ' - ',
    formatDate(parseDate(concat(
        substring({Your Field}, 9, 4), '-',
        substring({Your Field}, 13, 2), '-',
        substring({Your Field}, 15, 2)
    ), 'yyyy-MM-dd'), 'MMM/dd/yyyy')
)
1 Like
6

can u explain y u used formatedate function…is it needed?

7

concat(
parseDate(concat(
substring(toString(sum(DATE2)), 1, 4), ‘-’,
substring(toString(sum(DATE2)), 5, 2), ‘-’,
substring(toString(sum(DATE2)), 7, 2)
), ‘yyyy-MM-dd’)

parseDate(concat(
    substring(toString(sum(DATE2)), 9, 4), '-',
    substring(toString(sum(DATE2)), 13, 2), '-',
    substring(toString(sum(DATE2)), 15, 2)
), 'yyyy-MM-dd')
)

i modifed it in this way it throwing error…can u plz help me wd this

8

Hi @Siddhi_21

Using formatDate formats a date using a pattern you specify.

Please refer the below format date and supported date formats documentation this might be helpful for you.

9

Hi @Siddhi_21

Can you please remove aggregate function SUM and try?.

If you can recreate this issue in Arena using sample non-sensitive data, I can take a deeper look to see what is going on.

1 Like
10

image
image765×236 4.31 KB

its giving me this error

11

aws…

this is the arena version

12

can u plz look into it nd lemme know…how to acheive required solution…

13

image
image1163×378 38.2 KB

i want table like this…

14

Hi @Siddhi_21

Please check the following images. They were tested with your dataset, and some values in the DATE2 field from your dataset are incorrect.

image

image
image806×487 50.3 KB

Please refer the below pivot table documentation this might be helpful for you.

1 Like