.NET ticks to Oracle date 1

[tpremore]

I have a number that represents the value you get when you look at a .NET System.DateTime .Ticks property. This number is 633325517040000000. The problem is that I have this in an oracle table and I want to get a human readable date from it using PL SQL. I no longer have access to the handy .NET System.DateTime object that easily will convert this to something readable. Any Ideas, smart people?
-Travis

 

[ChrisHunt]

Hmmm...

According to

http://msdn2.microsoft.com/en-us/library/system.datetime.ticks(VS.71).aspx

[Ticks are] the number of 100-nanosecond intervals that have elapsed since 12:00 A.M., January 1, 0001.

So there are 10 million ticks in a second. To convert ticks into an Oracle date, you just have to work out how many days that number of ticks is, and add it to the start date. Something like this:

SELECT TO_DATE('01-jan-0001','dd-mon-yyyy') +
       ticks/(10000000 * 60 * 60 * 24) ticks_date
FROM   dual

Obviously, you could put that calculation into a PL/SQL function if you wanted to.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[tpremore]

Awesome, thanks Chris. It doesn't seem to work perfectly for all times from the year 0001 to modern times, but for everything back at least as far as 1595 it's only off by 2 days. And it's exactly 2 days, so I just start with Jan 3 0001 and I'm happy. It's probably some crazy Gregorian/Julian calender thing that there is a perfectly good explanation for so I'll leave it at this - I have a workable solution! Thanks,
Travis