I would like to add the time to an Excel file in the 24 hour format

Toad for IBM DB2
1

Hi,

When running an automation script that saves a SQL result to an excel file, there is the option of having the date and time appended to the file.

Is there any way to have the 24H format written?

In fact, is there a way to add AM / PM?

Thanks,

Shimon

2

Hello Shimon,

Could you please clarify: do you want to append it to sheet name? Or file name? Because the subject mentions “Excel sheet” but you wrote “date and time appended to the file”…

For file name there is the datetime suffix in the drop down of “Select to file” activity and for the sheet name you can click on the “Advanced properties” button located below the drop down and there you can check the option to add a timestamp. So when you are asking about AM/PM specifically i assume you know about these options and that the 24h format does not work for you - could you please confirm this?

Martin

3

Hi Martin,

I confirm that you understood correctly. I edited the subject of the post to “file” instead of sheet.

4

I believe that the “HH” format specifier in the combo box represents 12h format which doesn’t make much sense as there is not am/pm indicator, i will create a new task to get this changed for the future.

As a workaround please add SetVariable activity at the beginning of your script, add a string variable with the expression: DateTime(‘YYYY-MM-DD HH24:MI:SS’). Note that the HH24 format specifier represents the 24h format. Once you do it, then append the file name manually, like this: x:\Path\To\My\File\myReport_#myDateVar#.xlsx and let me know whether it helps for now.

Martin

5

Yup, it does the job.

Thanks alot,

Shimon