Convert to Date

Hello Community,

I’ve a date column in SQL table which is in (int) format yyyymmdd. I want to convert this to dd/mm/yyyy in a virtual column? Could you please let me know how I can achieve this.

Thanks

Create a virtual column and give below expression in Appformula.

CONCATENATE(RIGHT(“0” & DAY([DATE]),2),"/",RIGHT(“0” & MONTH([DATE]),2),"/",YEAR([DATE]))

Replece [ Date ] with you column name.

This will gives you in “dd/mm/yyyy” format.

2 Likes

@jithan
Thanks for the response. This might not work for as my actual date column is in integer format. eg: 20200616

Could you please try

CONCATENATE(RIGHT([DateColumn],2), “/”, MID([DateColumn], 5,2),"/", LEFT([DateColumn],4))

1 Like

@Suvrutt_Gurjar I’ve tried this. It is infact giving me dd/mm/yyyy but its in text format. When I change the Column type to Date it says “Invalid Date”

Thanks

Please try

DATE(CONCATENATE(RIGHT([DateColumn],2), “/”, MID([DateColumn], 5,2),"/", LEFT([DateColumn],4)))

1 Like

Its completely blank now.

Could you please share the column settings?

Instead of Date column type, keep Text data type

2 Likes

In the picture below the VC expression converts the text string of column called [Product Description] to a date. The expression is in a VC of date type.

You may need to shift between dd and mm expressions based on how your device and other components ( spreadsheet, AppSheet settings under Data- > Table ) have locale settings.

1 Like

This is a good suggestion by @gowtham . Unless you wish to have the date for any further date bassed calculation, I believe it is better to have it as a text type. Dates in general need to be handled with care with settings at a few places.

1 Like

Please try TEXT([DateColumn],“dd/mm/yyyy”)

1 Like

Hi,

Yes. When I test the expression I can see the result. However, the virtual column is blank in the table view. Moreover, I need to make calculate hours worked based on this date and that expressions isnt working.

Thanks

Hi Aleksi,

I’ve used your expression and it gives me the following error

TEXT function with two arguments requires a temporal type and a format string.

The sample date would be 20200616 in “Number” type in the date column. The desired result is 16/06/2020 so that I can use this column for other calculations.

I’ve another column which has the date in text format 16/06/2020.

Please advise.

As requested, could you please share the settings of the input date string column( yyyymmdd) and the column where the suggseted expression is filled in?

@Suvrutt_Gurjar

The integer [Date] column is a ‘Number’ Type. I used your expression to create a virtual column which will hold the date in proper format.

Thanks

Could you plese test with the expression in a real column.

Yes that’s correct, it doesn’t work with the number field… it needs to be date field,