Excel date formatting before writing in word document

Help Studio
, ,
1

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.

2

HI @Sunny_J

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

Regards
Sudharsan

1 Like
3

@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)

4

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?

5

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)

6

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

7

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.

8

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

9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.