convert long (java date.getTime) to sql server datetime

I think it depends upon the base value of Java.

I guess the time is correct as that part equates to 0.2144426389 which is 05:08:48

The remainder is 14,110.

So if you can convert the correct date in SQL to a number ...

SELECT CAST(CONVERT(DATETIME, '2008-09-10', 121) AS INTEGER)

This will give you the relative number of days from some base point.

The difference between Java's and SQL's would need to be added to Java's to turn a Java Date into an SQL Date.

I think.

Can you tell us what system it is coming from (not just Java), different machines can have differ Epoch's  so it does become important to know more about the origins...

And if there is not meant to be any time component (though the remainder does suggest otherwise), can you please let us know ?

A "standard" epoch for today midday is 1222480800 for example (in seconds, not milliseconds)

and 2008-09-10 is 1220968800 (or 1221012000 at midday - again in seconds, javascript)  or, 1221048000 (in seconds, at midday, this time unix)

So, we really need to know that "base" system

the base system is windows 2000 server
the application runs over ibm websphere 6.2.11

Have you tried ...

SELECT CAST(CONVERT(DATETIME, '2008-09-10', 121) AS INTEGER)

Yet?

Once you tell me the value you get for this, I can help you further.

The result from this command is:
39699

sorry for delay...

Thank you.

And what is the numeric value of the Java date?

Is 1219122527844 the equivalent of 2008-09-10?

@RQuadling, yep it works out to be 22 days different - the unix epoch difference is 25567. There is a small problem with some epochs converting 12:00:00 AM as midnight and so, can sometimes be out by twelve hours.

Would also like to confirm some examples from specific tests for dates and the resulting "magic number". Can you arrange ?

The websphere standard epoch is 01 Jan 1970, so having trouble accounting for the missing 22 days from your example. There are some settings where the epoch is defined in INI / config files. There are a couple of places and defined in the Webshere / IBM on line help e.g. can use getRepositoryEpoch() which might shed some more information...

Mark, If you want to supply any Java date values, I can evaluate them to SQL easily enough.

What I don't know is why there are 22 days missing.

Java epoch should be January 1st 1970 midnight GMT.

As the java datetime does encode timezone into the value, this could also upset the time part and potentially the date also and don't get me started with DST!

@rquadling, that is not the problem. The original post did just that. This is a follow on suggesting that the Java / Unix conversion given before is not the correct basis for the date example of 2008-09-10. Every "epoch" calculator I can find has been giving results as expected.

These are the ones tested so far without success:

Java,
Unix,
Webshere - though there is a setting and seems to be used for data synch type operations and by all accounts is more like a SQL timestamp ie does not neccessarily translate to a specific date time, rather is a has number based on that date time.
Win32 (1601)
Fat (1980)

and a few others...

Is 1219122527844 the equivalent of 2008-09-10?

YES, the date was transformed in java application using Date.getTime() function, and it results in 1219122527844 number

So, just change the value you need to add : 25589.

Yes, you can just add 22 days to the "standard" 1970 epoch of 22567 days

But please be warned, it does not represent any of the standard epochs. Have spent today researching and reviewing code to see where that number may have originated, and have not been able to find the basis for that number to be 2008-09-10...

Here are the more common "system" dates that I have checked :

January  1,  0    matlab  
January  1,  1    Symbian, Microsoft .NET, REXX This epoch date is known as Rata Die
January  1,  1601 FAT32 file system, COBOL  
December 31, 1840 MUMPS programming language 1841 was roughly the birthyear of the world's oldest living person when the language was designed.
November 17, 1858 VMS, United States Naval Observatory, other astronomy-related computations  
December 30, 1899 Microsoft COM DATE Technical internal value used by Microsoft Excel; to simplify calculations by falsely assuming 1900 to be a leap year.
January  0,  1900 Microsoft Excel, Lotus 1-2-3 While logically January 0, 1900 is equivalent to December 31, 1899, these systems do not allow users to specify the latter date.
January  1,  1900 Network Time Protocol, IBM CICS, Mathematica  
January  1,  1904 Apple Inc.'s Mac OS through version 9, Palm OS, MP4 1904 is the first leap year of the twentieth century.[4]
January  1,  1960 S-Plus, SAS  
January  1,  1968 Pick OS  
January  1,  1970 Unix time, used by UNIX, Linux, other UNIX-like systems, Mac OS X, Java, JavaScript  
January  1,  1978 AmigaOS  
January  1,  1980 MS DOS, OS/2, FAT16 filesystem  
January  6,  1980 Qualcomm BREW, GPS  
January  1,  2000 Various updated MS DOS legacy software


Also, here are some Days and Seconds for various gregorian dates (and differences between epochs) expressed using Julian calender as the basis for days...


--  1/1/0001 1721425.5 days or 148731163200.0 seconds
--  1/1/0100 1757949.5 days or 151886836800.0 Seconds
--  1/1/1601 2305813.5 days or 199222286400.0 Seconds
--  1/1/1900 2415020.5 days or 208657771200.0 Seconds
--  1/1/1970 2440587.5 days or 210866760000.0 Seconds
--  1/1/1980 2444239.5 days or 211182292800.0 Seconds
--  1/1/0100   36524.0 days or   3155673600.0 Seconds from day 1/1/1
--  1/1/1601  584388.0 days or  50491123200.0 Seconds from day 1/1/1
--  1/1/1900  693595.0 days or  59926608000.0 Seconds from day 1/1/1
--  1/1/1970  719162.0 days or  62135596800.0 Seconds from day 1/1/1
--  1/1/1980  722814.0 days or  62451129600.0 Seconds from day 1/1/1
--  1/1/1900  109207.0 days or   9435484800.0 Seconds from day 1/1/1601
--  1/1/1970  134774.0 days or  11644473600.0 Seconds from day 1/1/1601
--  1/1/1980  138426.0 days or  11960006400.0 Seconds from day 1/1/1601
--  1/1/1970   25567.0 days or   2208988800.0 Seconds from day 1/1/1900
--  1/1/1980   29219.0 days or   2524521600.0 Seconds from day 1/1/1900


Using some Java, can also assure you that :
   System.out.println ( "Date =" + dt); / / eg 'Mon Jan 26 19:03:56 GMT +01:00 2001'
   System.out.println ( "ms =" + dt.getTime ()); / / eg'980532236731 '
Corresponds to using the algorithm previously supplied using the 22567 days with 1970...

Also, think about the numbers... It is being represented as "ticks" assumed to be seconds (and tried the two more obscure calculators of 500 milliseconds, and 2 seconds)

 1219122527844 / (  60*60*24.0)  = 14110214.442638888888888 Days - which clearly, there is an order of magnitude (likely to be 1000).

Now clearly that has a multiplier affect - assumed to be unix / java therfore need to divide by 1000. Then using the small chart above (there is a bigger one, but you get the idea), I have not been able to arrive at the date you reported. There is one small problem in so much as it may be twelve hours different due to the interpretation of 12:00:00 so the time component could well be the afternoon, not morning...

So, again please be careful with that base date. And please let me know down the track if you are still happily using that base - would like to know, and will continue my research for that translation.

I do hope to hear from you again - down the track...