General
Enthusiast

TS to MS

Input:

08/05/2018 17:18:10.455000

Output should be as below:

1,525,799,890,455

Accepted Solutions
Junior Contributor

Re: TS to MS

Multiply first, then cast:

```Cast((Cast((Cast(ts AS DATE) - DATE '1970-01-01') AS DECIMAL(38,6)) * 86400
+ (Extract(HOUR From ts) * 3600)+ (Extract(MINUTE From ts) * 60)+ (Extract(SECOND From ts)  )) * 1000 AS BIGINT)```
1 ACCEPTED SOLUTION
8 REPLIES 8

Re: TS to MS

Check this post from DNoeth.

Enthusiast

Re: TS to MS

Thanks Fred.

But the output is displayed with decimals. I would need it without decimals.

Something like this, 1,525,799,890,455

and 1,525,799,890.455000

SyntaxEditor Code Snippet

```(CAST((CAST(ts AS DATE) - DATE '1970-01-01') AS decimal(18,6)) * 86400
+ (EXTRACT(HOUR FROM ts) * 3600)+ (EXTRACT(MINUTE FROM ts) * 60)+ (EXTRACT(SECOND FROM ts)  ))```

Junior Contributor

Multiply * 1000?

Highlighted
Enthusiast

Re: TS to MS

Nope Dnoeth, it doesnt work when I multiply by 1000.

Junior Contributor

Re: TS to MS

Doesn't work is a very precise error description.

Of course you can cast to a BigInt after multiplicdation.

Enthusiast

Re: TS to MS

Sorry fro not being precise.

Am expecting output to be something like,

1525799890455

whereas my query below gives me as 1525799890.455

Casting it to bigint leave last 3 values, and thats not expected too. Multiplying doesnt work too. Might be silly, but am stuck somewhere here.

SyntaxEditor Code Snippet

```(CAST((CAST(ts AS DATE) - DATE '1970-01-01') AS decimal(18,6)) * 86400
+ (EXTRACT(HOUR FROM ts) * 3600)+ (EXTRACT(MINUTE FROM ts) * 60)+ (EXTRACT(SECOND FROM ts) ) )```

Junior Contributor

Re: TS to MS

Multiply first, then cast:

```Cast((Cast((Cast(ts AS DATE) - DATE '1970-01-01') AS DECIMAL(38,6)) * 86400
+ (Extract(HOUR From ts) * 3600)+ (Extract(MINUTE From ts) * 60)+ (Extract(SECOND From ts)  )) * 1000 AS BIGINT)```
Enthusiast

Re: TS to MS

Thanks, it worked now!

One quick clarification,Can we use strtok function for null values?

If no, other than CsvLd, do we have any other functions? Columns are predefined and no.of columns will not differ.