Convert to Date

[Kranthi_Kumar]

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

[jithan]

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.

[Kranthi_Kumar]

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

[Suvrutt_Gurjar]

Could you please try

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

[Kranthi_Kumar]

@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

[Suvrutt_Gurjar]

Please try

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

[Gowtham]

Instead of Date column type, keep Text data type

[Suvrutt_Gurjar]

Gowtham:

Instead of Date column type, keep Text data type

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.

[Kranthi_Kumar]

Its completely blank now.

[Suvrutt_Gurjar]

Could you please share the column settings?

[Suvrutt_Gurjar]

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.

[Kranthi_Kumar]

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

[Suvrutt_Gurjar]

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?

[Aleksi]

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

[Kranthi_Kumar]

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.

[Aleksi]

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

[Kranthi_Kumar]

@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

[Suvrutt_Gurjar]

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

[lala_land]

Hi Kumar, after reading your post, I would like to check with you how do you convert the date column (integer) format yyyymmdd into 16/06/2020 so that you can use this column for other calculations.

I am encountering the same issue as you. Is it possible for you to share your solution and what expression you use to solve this issue?

Thank you and looking forward to your reply.

[Suvrutt_Gurjar]

I think you will need to do it in a somewhat brute manner as below

DATE(CONCATENATE(RIGHT([DateInteger],2),"/", MID([DateInteger], 5,2),"/", LEFT([DateInteger], 4)))

The [DateInteger] is assumed to be a number or Text type column with a date format of yyyymmdd and the date format throughout in your app is assumed to be in DD/MM/YYYY