How to convert a value in miliseconds to a formatted date with Table Transformer

[Bastian Stehmann]

Hi,

I have a problem with table transformer app.

I have a table with a column with a date and want to subtract some days from that and display the result as formatted date again.

I already tried different approaches, but non worked.

My current approach is this:

SELECT 
'Column1', SUBSTRING(CONVERT(VARCHAR,CONVERT(DATETIME,(CAST('Column1' as milliseconds) - 5184000000 - 1209600000))),1,10) as 'Date -60 and -14 days' 
FROM T*

Any ideas how to solve this?

[Andrey Khaneev _StiltSoft_ · Accepted solution]

Hi Bastian,

First, here are some tips:

1.You can operate with a date column like with a number of milliseconds without any converting. For example:

'Date column' - 5184000000

2. Instead of using number of milliseconds, which is hard to read, you can use number of hours: 

'Date column' - "24h"

Or use number of days:

'Date column' - "14d"

But make sure the day is set to 24 hours in settings:

This variables can be used for time tracking processing, that's why number of hours in a day is configurable.

3. To format the new column as a date column you can use three different variants:

a. Output the new column as the original column:

'Date column' - "14d" AS 'Date column'

b. Add a dummy table with the new column with a date inside:

'Date column' - "14d" - "60d" AS 'Date -60 and -14 days'

c. Format the date manually:

SELECT 'Date', 'Something',
MONTH( 'Date before' ) + "/" + DAY( 'Date before' ) + "/" + YEAR('Date before' ) AS 'New date'
FROM (
SELECT *, 
'Date' - "60d" - "14d" AS 'Date before' 
FROM T1)

[Oliver Schukay]

Hi @Andrey Khaneev _StiltSoft_ thanks for your support! But it still doesn't work for us to see the result formated as a date (like the original one in the left column)...

we have it done like this:

using this jql:

SELECT 'Production Plan OTN',
DAY( 'Date before' ) + "/" + MONTH( 'Date before' ) + "/" + YEAR('Date before' ) AS 'New date'
FROM (
SELECT *, 
'Production Plan OTN' - "60d" - "14d" AS 'Date before' 
FROM T1)

and still having this result:

Can you see, what we are doing wrong?

Best regards & many thanks in advance!

[Andrey Khaneev _StiltSoft_]

Hi Oliver,

You can do this trick:

1. Add another table with two cells "New date" and a //dateMacro 
2. Simplify the SQL to SELECT *, 'Date' - "60d" - "14d" AS 'New date'  FROM T1
3. And get this result 

[Oliver Schukay]

THANKS Andrey!

That really looks so much better now and our colleages needn't to fill in the tables columnwise manually!

[Oliver Schukay]

Sorry @Andrey Khaneev _StiltSoft_ Andrey,

may I ask you another question on this?

And how do I add another (or multiple) new columns with other operators (e.g. +14 days in another column). Is it also possible to have an influence on the sequence of the new columns (e.g. column is included before the original column)?

[Oliver Schukay]

I found out how to insert additionally columns (trial and error by following the logic of your "Trick"), but what about the sequence?

[Oliver Schukay]

@Andrey Khaneev _StiltSoft_we also found out. Now I'm perfectly happy! Thanks again!

[Andrey Khaneev _StiltSoft_]

If you need to add a column somewhere in the middle of the table, then you have to list all columns of the table:

SELECT 'Column A', 'Column B', 'Date', 'Date' + "14d" AS 'New Date', 
'Column C', 'Cost' * 'Count' * 1.20 AS 'Price', 'Column D' FROM T1

[Bastian Stehmann]

Hi @Andrey Khaneev _StiltSoft_ 

thank you very much for helping us here.