Excel date formatting before writing in word document

[Sunny_J (Sunny Jha)]

Hi,
I have an output datatable from database, few of those columns have datetime in the format (yyyy-MM-dd HH:mm:ss). What I need is to convert those date columns in yyyy-MM-dd format and then I am printing that table to word document using word activities(so that I can get a table like structure).

I have tried a for each row after fetching the datatable from database and using an assign activity row(“Column”.Trim) = Convert.ToDateTime(row(“Column”.Trim).ToString).ToString(“yyyy-MM-dd”)

But when I reference the datatable in the word activity, these columns are not getting updated. Am I missing any step here? Do share your insights!

PS: The datatable has only 1 output row so I don’t mind using loop in place of linq queries/vb scripts.

[Sudharsan_Ka]

HI @Sunny_J

you change the format of the date by using change cell type activity and look into this thread for the steps

UiPathGo - BalaReva Excel Activities

Hi , In the release Version 2019.2.0. I have added a new activity called Change Cell Type. It changes the format of the value like below. [image] In the Cell Format , can specify the custom format what we have in the excel Like below image. [image] Sample : UiPathGoExcel.zip (18.4 KB) This is the video to configure the package in local. Here is the link to know about the formats. Regards Balamurugan.S

Regards
Sudharsan

[ushu (Usha kiranmai)]

@Sunny_J Try this CDate(CurrentRow(0).ToString).ToString(“yyyy-MM-dd”)

Here 0 represents column index, you can change this based on the column in which the data was present . Please find below workflow

DT.zip (2.5 KB)

[postwick (Paul)]

First off, why do you have .Trim after “Column”? Don’t do that.

You’re literally referencing a column named Column. Is that your intent?

Datetimes don’t have formats. They are stored as milliseconds from Jan 1 1970, and you format them when you output them as a string.

Is the datatable’s column actually datatype datetime? Or is it string?

[Sunny_J (Sunny Jha)]

hi,
I saw the build datatable activity you used, the column format is a string, maybe that’s why its working. But as I said I am fetching the table from the database and the column is of datetime format.

I have attached the workflow please look at it again.
DT.7z (34.3 KB)

[Sunny_J (Sunny Jha)]

Hey,
I used “column” just as an example here
Yes the table that’s fetched from the database has the column as datetime format not string.

[postwick (Paul)]

OK. But don’t put .Trim on the end of the column name. .Trim is for removing extra spaces from the beginning and end of the string. No need for that, just reference the correct column name.

If you need to change how the dates are output, you’ll add a string column to the datatable, For Each Row and convert the datetime to the string format you want, into the string column. Remove the datetime column. Write the datatable and you’ll get the result you want.

[Sunny_J (Sunny Jha)]

I made a change to the sql query itself when fetching dates from the database using the convert function with the format code.
Reference : Convert DateTime To Different Formats In SQL Server
Using SQL CONVERT Date formats and Functions - Database Management - Blogs - Quest Community