This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes how to convert float datatype to date Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to convert float datatype to date" Watch "how to convert float datatype to date" New topic
Author

how to convert float datatype to date

Kee Kee moon
Ranch Hand

Joined: Dec 11, 2009
Posts: 147
I have microsoft sql server contains a table with a column that datetype is float.
One of the columns its datetype name "dateColumn" and it declared as float like 30268.6859751159.
I need to use the function java.sql.Timestamp dateCStamp = getResult.getTimestamp(dateColumn);

The exact error I got is "com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from float to TIMESTAMP is unsupported."
How to convert the float datatye to date? The float date like 30268.6859751159
and I need to convert the data 30268.6859751159 to date by using java.sql.......
Please help.
Thanks






Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

Hi,

Do you know what date/time is represented by 30268.6859751159 ?

Regards, Jan


OCUP UML fundamental and ITIL foundation
youtube channel
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

This isn't really a JDBC question (although I'll leave it here for now). If it's being stored as a float, then its not a date. SQL server can store dates as dates.

In short, you have to do the conversion yourself. Figure out how the date was converted to float in the first place and reverse it. Java does tend to use epoch time (long numbers to represent time) but does not appear to be related to your storage method.


My Blog: Down Home Country Coding with Scott Selikoff
Kee Kee moon
Ranch Hand

Joined: Dec 11, 2009
Posts: 147
Jan Cumps wrote:Hi,

Do you know what date/time is represented by 30268.6859751159 ?

Regards, Jan




If I use sql server statement to convert to date as below, it returns 1982-11-15 16:27:48:250.
select convert(datetime,30268.6859751159);

As now, I need to use java.sql.Timestamp dateStamp = getResult.getTimestamp(dateColumn);
And I got an error as "com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from float to TIMESTAMP is unsupported."
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

Kee Kee moon wrote:If I use sql server statement to convert to date as below, it returns 1982-11-15 16:27:48:250.
select convert(datetime,30268.6859751159);[/b]."


Then do that. Use a convert() in your SQL statement. If the raw datatype is float, then how is the JDBC driver supposed to know how to convert it? Really, your database designer should have used a date datatype in the field, but since they didn't you'll need to call convert from your SQL statement which should then allow you to read it via JDBC. I might even need to insert a CAST() there, SQL server is kind of odd like that.

Alternatively, you could convert the float in Java. It would keep your SQL statement simple but make your Java code overly tied to your database.
Kee Kee moon
Ranch Hand

Joined: Dec 11, 2009
Posts: 147
Scott Selikoff wrote:
Kee Kee moon wrote:If I use sql server statement to convert to date as below, it returns 1982-11-15 16:27:48:250.
select convert(datetime,30268.6859751159);[/b]."


Then do that. Use a convert() in your SQL statement. If the raw datatype is float, then how is the JDBC driver supposed to know how to convert it? Really, your database designer should have used a date datatype in the field, but since they didn't you'll need to call convert from your SQL statement which should then allow you to read it via JDBC. I might even need to insert a CAST() there, SQL server is kind of odd like that.

Alternatively, you could convert the float in Java. It would keep your SQL statement simple but make your Java code overly tied to your database.


I use Microsoft sql server.
I haven't solved the problem yet. Please help if anyone of you know how to.


String strDate = "select convert(datetime,40166.6859751156)";
convert2Date = connection.prepareStatement(strDate);

// I got a return value as com.microsoft.sqlserver.jdbc.SQLServerResultSet@861f24 if I use System.out.print
// How to a readable value and I can print the return the return value. Which function should I call in order
// to get a readable return value.
retDate = convert2Date.executeQuery();
retDate = convert2Date.getResultSet();





retDate.close();

Kee Kee moon
Ranch Hand

Joined: Dec 11, 2009
Posts: 147
Kee Kee moon wrote:I have microsoft sql server contains a table with a column that datetype is float.
One of the columns its datetype name "dateColumn" and it declared as float like 30268.6859751159.
I need to use the function java.sql.Timestamp dateCStamp = getResult.getTimestamp(dateColumn);

The exact error I got is "com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from float to TIMESTAMP is unsupported."
How to convert the float datatye to date? The float date like 30268.6859751159
and I need to convert the data 30268.6859751159 to date by using java.sql.......
Please help.
Thanks




I have resolved the problem.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

That's wonderful. Please post the solution so that others may learn from this issue.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to convert float datatype to date
 
Similar Threads
How to convert String to float with exact decimal value
How to convert timeStamp into corresponding date.
String to Date Conversion
Using CacheRowset
string to float conversion round off problem