Skip to main content
  • EN
Hello,

 

I have a db table with a field called CSTSTARTDATEDA. In the db table, an example of a value is '8/16/1999'. In FME, I am using a SQL Server reader to pull all the data in the table, and FME is showing the date as 19990815000000.000.

 

 

I need to convert the date to milliseconds format (a requirement for a data migration project). If I were doing this in SQL, I would convert the date to a BigInt, then subtract 25568 and multiply by 86400000. This would give me 934848000000 ((Convert(BigInt,ICSTSTARTDATEDA])-25568)*86400000). I tried to do this in my SQL statement in FME, but FME would not accept it.

 

 

Is there another way to do this? I'm open to using a Python caller, but I'm not too familiar with Python.

 

 

And the final result has to be in this format. There is no way around it.

 

 

Thanks for any help/ideas!

 

 

Erin

 

 

Amending this to say that actually Excel and Access would give me a value of 934848000000, not SQL. But either way, 934848000000 is the value I would need
I think ideed thats sql server and sql transact.

 

 TO_NUMBER, TO_CHar etc.  in oracle sql.

 

 

But (CSTSTARTDATEDA-25568)*86400000 works fine in fme sql creator
soz, its a date of course,

 

 

TO_NUMBER function u need s

 

 

http://www.tutorialspoint.com/sql_certificate/conversion_functions.htm

 

 

 

I appreciate the quick response. Unfortunately I can't get that to work. Let's say I did this in Excel. I would have (8/15/1999 - 25568)*86400000. This would yield 934848000000. Because in Excel, it would know that 8/15/1999 equals 36388 (in other words, 36,388 days since 1/1/900).

 

 

In FME Sql creator, I can't actually get (CSTSTARTDATEDA - 255628)*86400000 to work. I get an error with the SQL Statement. If I take it out, it works fine. However, even if it did work, I think it would just subtract 25568 from 19990816000000.000. But that wouldn't return the value I would expect.

 

 

So, maybe there some other way? I think if I could get the date to convert to the number of days since 1/1/900, then I could complete the calculation. But I can't seem to figure that out.

 

 

Thanks!!
Ok, I think I've figured it out now. I think I just needed some new perspective, so thank you for that!

 

I used DateDiff and just subtracted out 12/31/1969 (equivalent of 25568). This seems to get me the value I need to multiply by 86400000 to get to milliseconds

 

 

Thanks!!!

Reply


Community Stats

30,108
Posts
114,295
Replies
37,743
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.