SQL Server datetime in Java?
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5

Thread: SQL Server datetime in Java?

  1. #1
    Join Date
    Sep 2000
    Posts
    27

    SQL Server datetime in Java?

    I'm using JDBC to send updates to a SQL server 7.0 database.

    I can't work out how to send the parameters required to set a datetime field.

    java.sql has classes for date (with no time) and time (with no date). But SQL Server does not support date and time types, only the combined datetime type.

    There must be a conventional way of getting the data from Java (held as a java.util.date class) to SQL Server.

    I know I could convert to and from a string but thats a bit messy and open to error due to time-region settings.

    Any ideas ?




  2. #2
    Join Date
    Dec 2000
    Location
    Belgium
    Posts
    264

    Re: SQL Server datetime in Java?

    Hi Mr Bump,

    I just want to register myself as "interested too". Tackling this problem is on my "TO DO" list. I have an Informix DB, which has also a DATETIME field.

    There is one other class, I think could work (according to specs):
    java.sql.TimeStamp.
    Stores everything up to seconds. But, but, but: not tested yet, so I do not guarantee.

    If this does not work, I will put the time in string format. Since my application is not going outside Belgium, it should work without worrying about region. However, the normal toString() format does includes time-region settings. Messy, but you can make it work.

    Regards,
    Geert Arys

  3. #3
    Join Date
    Sep 2000
    Posts
    27

    Re: SQL Server datetime in Java?

    From a test in SQL Server, I don't think the timestamp and datetime types are interchangeable. timestamp does not have a day,month,year concept - its just used for stamping record inserts and updates. If you convert a date to a java.sql.timestamp then to a SQL Server datetime, you get gobbledeegook (UK term for rubbish!).

    My workaround is to :
    (1)From Java, send a java.sql.date and a java.sql.time parameter to a stored procedure

    (2)In the stored proceure, receive both parameters as datetime type.

    (3)In the Stored Procedure, add the relevant bits of the "time" parameter to the date to create a combined datetime. Insert this into the table.

    This gets round any string conversion issues, but obviously requires SQL code to recombine the date and time, so would be no good for a plain INSERT or UPDATE.

    As for your code never leaving Belgium - there's still a danger that a client will have different time zone settings.


  4. #4
    Join Date
    Dec 2000
    Location
    Belgium
    Posts
    264

    Re: SQL Server datetime in Java?

    Hi,

    Thanks,

    About the java.sql.Timestamp. You get gobbledeegook? :-) Figures.

    I think you have a good workaround. Unfortunately not workable in my architecture (since some SQLs can be generated dynamically in our framework).

    About, the clients, no prob. They don't know any database. Only servers know. They are known and controlled. Anyways, I'll need to be careful.

    So, I'm afraid I'll have no other choice than to make two columns in my tables. One for Date, one for Time. If TIME exists in Informix, I'll have to check. It probably will.

    Regards,
    Geert Arys

  5. #5
    dlorde is offline Elite Member Power Poster
    Join Date
    Aug 1999
    Location
    UK
    Posts
    10,163

    Re: SQL Server datetime in Java?

    You'll find that both java.sql.Date and java.sql.Time subclass java.util.Date. They both store the same information, but wrap it slightly differently, so you should be able to use both to set the SQL Server combined date/time type.

    Internally, the representation of both is in milliseconds since January 1st 1970 00:00:00 GMT.

    We use Oracle with its DATE type that combines date and time, but it should be similar. When a Java Time gets stored in the database, the database DATE value shows that time since 1st Jan 1970, i.e. 01/01/70 hh:mm:ss. I would guess that the database date type is very similar to the Java date and time types, but storing seconds rather than milliseconds...

    The only problems I've encountered were in searching for a time value, where it didn't work properly, so I ended up formatting the search Time to a String and comparing it with the SQL character expression TO_CHAR(db_time, 'HH24:MI') - where db_time is the DATE field with the time value in it:String start = (DateFormat.getTimeInstance().format(searchItem.getStartTime())).substring(0, 5);
    String query = "SELECT * FROM mytable WHERE TO_CHAR(starttime, 'HH24:MI') = '" + start + "'";
    ...

    Dave



    To email me remove '_spamjam' from my email address
    Please use [CODE]...your code here...[/CODE] tags when posting code. If you get an error, please post the full error message and stack trace, if present.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center