aspose file tools*
The moose likes Beginning Java and the fly likes Convert this to a date? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Java » Beginning Java
Bookmark "Convert this to a date?" Watch "Convert this to a date?" New topic
Author

Convert this to a date?

Vinnie Jenks
Ranch Hand

Joined: Apr 26, 2004
Posts: 207
I've got a field in a database table that is supposed to be a date but it's an integer...and it keeps defaulting to 1/14/1970. It's supposed to be 2/16/06.

Now, I realize that a default date in Java is 1/1/1970 - but how do I convert this to the correct date? I'm just not putting 2+2 together and I'm now out of time.

Can someone help?

Thanks!
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16

If you show us what you're really doing there, we may help.
Send us the code where you're creating the date.


[My Blog]
All roads lead to JavaRanch
Vinnie Jenks
Ranch Hand

Joined: Apr 26, 2004
Posts: 207
OK, here's where it gets complicated...it's pretty much what I explained. Here's some detail.

I'm building an JPA/EJB3 app using Glassfish. I have an entity mapped to a table that has a bunch of fields that are dates...but the field is an integer data-type in MSSQL 2000.

The field looks like this in the entity (I have no choice, it's a legacy database):



The field called 'last_mod_date' in this table has a value of 1109092272 for one of the records. I assume this is time in milliseconds...but when displayed it comes out 1/14/1970. The real value for this field should be 2/16/06.

As for code, here's the best I can do as I'm really just guessing as to what to do:



Sorry I can't provide much detail, that's really all there is to it.
Vinnie Jenks
Ranch Hand

Joined: Apr 26, 2004
Posts: 207
It's not a silly question but I already answered it - it's a legacy database which I did not create, have no control over, and have no choice but to deal with it. It's our service desk database and it was created for an application we purchase from a 3rd. party software vendor. I'm just tasked w/ pulling some read-only data from it.

I know for a fact it's a date but I do not know from what date it might be calculated. I also told you that the field is a Microsoft SQL Server 2000 integer...an 'int' field.

What I can tell you is that another co-worker is able to format it in crystal reports, she uses the following formula to convert it:

cdate(dateadd('s',({chg.last_mod_dt}),#Jan 1, 1970#))

I'm not familiar w/ Crystal Reports but from what I can guess, she's adding seconds to the field, calculated from 1/1/1970.

SO, this tells me that there must be *some* way of doing this in Java.
Henry Wong
author
Sheriff

Joined: Sep 28, 2004
Posts: 18141
    
  39

Well, it is definitely not milliseconds from 1970. It is not seconds from 1970. I also tried a few other combinations which I ran into in the past -- nope.

The best I can recommend is to do research on the date format for the database that you are using. Whatever the format is, finding it by dumb-luck is not very effective here.

Sorry,
Henry


Books: Java Threads, 3rd Edition, Jini in a Nutshell, and Java Gems (contributor)
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16

field is an integer data-type in MSSQL 2000.

Representing the time in milliseconds ?
From which date is this referring to ?
Vinnie Jenks
Ranch Hand

Joined: Apr 26, 2004
Posts: 207
Originally posted by Henry Wong:
Well, it is definitely not milliseconds from 1970. It is not seconds from 1970. I also tried a few other combinations which I ran into in the past -- nope.

The best I can recommend is to do research on the date format for the database that you are using. Whatever the format is, finding it by dumb-luck is not very effective here.

Sorry,
Henry


Thanks for looking at it for me. However, how will studying the date format for my database do me any good when the field is an integer? It's a number - not a date. I *wish* it were stored in a DateTime field but it's not.

This is starting to look futile...I'm not sure what else I can do. If it can be converted in other applications, i.e. Crystal Reports, surely there's a way to do it in Java?

I don't know from what date it's being calculated...it's all proprietary - I've just got a int stored in a field that is most definitely supposed to represent a date.

I apologize if I seem frustrated...I do appreciate the help...I've just been doing this all day.
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16

I assume this is time in milliseconds

You've got to understand what this integer really means before processing it.
Assuming is not good enough. Refer to your database specification to understand what this "last_mod_dt" really is.

A little guess.
Wouldn't there be a "create_datt" column too, in date format ? (set to Feb-03-2006)
[ July 20, 2006: Message edited by: Satou kurinosuke ]
Vinnie Jenks
Ranch Hand

Joined: Apr 26, 2004
Posts: 207
Originally posted by Satou kurinosuke:

You've got to understand what this integer really means before processing it.
Assuming is not good enough. Refer to your database specification to understand what this "last_mod_dt" really is.


It is the arbitrary name that they gave to the column, which is simply an integer. The *only* documentation I have about this is a sheet that lists the fields and their types...I just found this while digging through our manuals.

It says that the field'd data-type is "LOCAL_TIME" - however this tells me nothing since there is no such data-type in SQL Server 2000.

Does this mean anything to you?
Vinnie Jenks
Ranch Hand

Joined: Apr 26, 2004
Posts: 207
Originally posted by Satou kurinosuke:

A little guess.
Wouldn't there be a "create_datt" column too, in date format ? (set to Feb-03-2006)

[ July 20, 2006: Message edited by: Satou kurinosuke ]


Yes, there is, there are a *lot* of date fields in the table - all of them are integers like the 'last_mod_dt' field - and do not offer any clues.

I know what the dates *should* be as I can run the crystal report and see the real values...but that doesn't help me either, of course.
Henry Wong
author
Sheriff

Joined: Sep 28, 2004
Posts: 18141
    
  39

Originally posted by Vinnie Jenks:

Thanks for looking at it for me. However, how will studying the date format for my database do me any good when the field is an integer? It's a number - not a date. I *wish* it were stored in a DateTime field but it's not.

This is starting to look futile...I'm not sure what else I can do. If it can be converted in other applications, i.e. Crystal Reports, surely there's a way to do it in Java?

I don't know from what date it's being calculated...it's all proprietary - I've just got a int stored in a field that is most definitely supposed to represent a date.

I apologize if I seem frustrated...I do appreciate the help...I've just been doing this all day.



Okay... do you happen to have *TWO* known values with their corresponding dates? If you do...

- Take a difference of those two values.
- Take a difference of those two known dates -- it doesn't matter if the difference is seconds, minutes, days, etc. You just have to be consistent.

Since you are working with differences, it doesn't matter what the starting date is. Take the ratio, which should help you figure out whether it is seconds, milliseconds, days, or some weird multiplier.

Once you know what the unit is, you can take either one of the two values. Multiply by the ratio to find the number of seconds, days, etc. And then subtract from the known date to fine the starting date.

Of course, all of this assumes a fixed starting date and a linear ratio to some unit. To confirm this, you need a third known date to check.

Henry
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16

If these were seconds, you would get "Feb 22 2005".
You are absolutely sure about getting "2/16/06" ?

Isn't there a way to know how Crystal Report interprets this number ?
Vinnie Jenks
Ranch Hand

Joined: Apr 26, 2004
Posts: 207
Originally posted by Satou kurinosuke:
If these were seconds, you would get "Feb 22 2005".
You are absolutely sure about getting "2/16/06" ?

Isn't there a way to know how Crystal Report interprets this number ?


I may not have posted the right integer value from the database...could you show me how you arrived to that given the number I provided?

I would *really* appreciate seeing how you arrived at that value!
Henry Wong
author
Sheriff

Joined: Sep 28, 2004
Posts: 18141
    
  39

Originally posted by Satou kurinosuke:
If these were seconds, you would get "Feb 22 2005".
You are absolutely sure about getting "2/16/06" ?

Isn't there a way to know how Crystal Report interprets this number ?


Yea... I already confirmed this. If it were seconds from 1970 -- it would be off by a year.


BTW, I just finished a program that does the operation that I talked about. If you give me 2 known values with their corresponding dates, I can calculate the base date, and the units of the value.

Henry
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16

The concept is that the integer stored in the Unix time, meaning the number of seconds elapsed from 1970/1/1. (GMT time zone)



In SQL Server:
select dateadd(dd, (1109092272/86400), '1/1/1970')

[ July 20, 2006: Message edited by: Satou kurinosuke ]
[ July 20, 2006: Message edited by: Satou kurinosuke ]
Henry Wong
author
Sheriff

Joined: Sep 28, 2004
Posts: 18141
    
  39

I may not have posted the right integer value from the database...could you show me how you arrived to that given the number I provided?

I would *really* appreciate seeing how you arrived at that value!


What?!?!? You spent a whole day trying to confirm a formula, when you weren't sure of the value?

Try...



Henry
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16

What?!?!? You spent a whole day trying to confirm a formula, when you weren't sure of the value?



It really smells like a Unix data. If not, I'm afraid I can't help more.
Henry Wong
author
Sheriff

Joined: Sep 28, 2004
Posts: 18141
    
  39

Originally posted by Satou kurinosuke:

It really smells like a Unix data. If not, I'm afraid I can't help more.


If it is not a Unix date, but has a fixed based date, and a fixed ratio for the unit value -- then the base date and units of the value can be calculated, using two known values as follows:



Henry
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Convert this to a date?
 
Similar Threads
Date class - GeekWatch
please help on a sql query
Date(long date)
converting sqldates?
Dates