how to convert string 2019.06 into date 2019-06

[ankit_singh_]

how to convert string 2019.06 into date 2019-06

[RolandSchubert]

Hi @ankit_singh_ ,

you could use the function DateTimeParse to convert the string to a date, DateTimeParse([Field1], '%Y.%m') would result in the date 2019-06-01 (day is missing in the string, so 1st of month is assumed). If you need "2019-06" as an output (would be a string field) you could use DateTimeFormat(DateTimeParse([Field1], '%Y.%m'), '%Y-%m') thereby formating the date in the required format YYYY-MM. This would be possible using simple replacement (e.g. Replace([Field1], '.', '-')

Hope this is helpful.

Best,

Roland

[delilah]

@ankit_singh_ Formula tool and DateTime Parse tool both work with this.

[ankit_singh_]

Thanks for your valuable idea but i want to convert the data type of this field1 to date, so let me know in which way i can achieve it?

[TomWelgemoed]

Hi @ankit_singh_ ,

I think this is the easiest approach - just use the datetime parse option - see image attached. You'll get an additional field in date format and retain the original field.

[ankit_singh_]

Thanks for your valuable idea but i want to convert the data type of this field1 to date datatype and want my output to be 2019-06

so let me know in which way i can achieve it?

[TomWelgemoed · Accepted solution]

Hi,

It can't be a date and only a year and month, as that is not a valid date format. Technically speaking (you'll see this even in databases), '2019-06-01' is a year & month, but the date format requires that a value is entered for the day (even if it is irrelevant). So for the entire month of June 2019, the date version of the month is 2019-06-01. 

To add, the 2nd column in my example is of a date format - so you can just use that if it must be in date format. If you can live with a string, you can simply use a substring in a formula tool instead.

[ankit_singh_]

Thanks Tom for your valuable assistance!

[TomWelgemoed]

Happy to help! All the best.