Skip to main content
Question

Need to convert date to milliseconds format

E
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,[CSTSTARTDATEDA])-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

 

 

5 replies

E
  • erin
  • Author
  • July 9, 2014
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
gio
Contributor
Forum|alt.badge.img+15
  • gioContributor
  • Contributor
  • July 9, 2014
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
gio
Contributor
Forum|alt.badge.img+15
  • gioContributor
  • Contributor
  • July 9, 2014
soz, its a date of course,

 

 

TO_NUMBER function u need s

 

 

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

 

 

 

E
  • erin
  • Author
  • July 9, 2014
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!!
E
  • erin
  • Author
  • July 9, 2014
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


Helpful Members This Week

Community Stats

31,153
Posts
118,105
Replies
38,772
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.