Why can't I save seconds in a timestamp in SQL Server
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Max_Mirabito
Posted On:   Monday, December 3, 2001 11:38 AM

When I save the a timestamp value to SQL server datetime column the seconds are not saved. for example if the java code returns 2001-12-03 12:42:13.104 the value actually saved is 2001-12-03 12:42:00.000. any help on the matter is greatly appreciated. Thanks Max *************************************************** Sample code: import java.util.*; import java.sql.*; import java.text.*; public class TestDate { static public void main(String argv[]) { try { System.out.println("Class Begin.."); Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //loads the driver Connec   More>>

When I save the a timestamp value to SQL server datetime column the seconds are not
saved.


for example if the java code returns 2001-12-03 12:42:13.104 the value actually saved is
2001-12-03 12:42:00.000. any help on the matter is greatly appreciated.


Thanks

Max


***************************************************


Sample code:

			

import java.util.*;
import java.sql.*;
import java.text.*;

public class TestDate {
static public void main(String argv[]) {
try {
System.out.println("Class Begin..");
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //loads the driver
Connection con = DriverManager.getConnection("jdbc:odbc:QHST-OD-NTSQL", "xxxx", "xxx");
Calendar cal = Calendar.getInstance();
Timestamp ts = new Timestamp(cal.getTime().getTime());
System.out.println(ts.toString());
String Sql = "INSERT INTO eflow..myDates(myDate) VALUES (?)" ;
PreparedStatement stmt = con.prepareStatement(Sql);
stmt.setTimestamp(1, ts, cal);
stmt.executeUpdate();
stmt.close();
con.close();
}
catch (Exception e) {
System.out.println("Insert Bean: " + e);
}
}
}

   <<Less

Re: Why can't I save seconds in a timestamp in SQL Server

Posted By:   Bernie_Acs  
Posted On:   Monday, December 3, 2001 01:43 PM

Based on your description of the problem I would venture a guess that the Date-Time value is being truncated on insert when passed using the Object type Timestamp. There may be some magic you can gleam from the following set of links which all seem to be leading to the 8 byte internal format that dates are stored in SQLServer.


I test an ACCESS datetime field with set/getTimstamp( Timestamp ) and found that the milliseconds element was truncated to 0 but the seconds element was preserved and valid.


My code snipplet looks like:



PreparedStatement pstmt1 = conctn.prepareStatement(
"insert into table2 ( col1, col2, date1 ) values ( ? , ?,? )" );
pstmt1.setString( 1, "testdate1" );
pstmt1.setInt( 2, 246 );
pstmt1.setTimestamp( 3, new Timestamp( System.currentTimeMillis() ), java.util.Calendar.getInstance() );

if( pstmt1.executeUpdate() > 0 )
System.out.println( "Did it!");
else
System.out.println( "Failed it!");

conctn.commit();
pstmt1.close();




SQLServer DateTime Datatype Information Links

  • How DateTime is Stored


  • DATEPART()


  • UDFexamples


  • A Semi-Related Article


  • Find sp_fixedstring_to_datetime.sql on this page
  • About | Sitemap | Contact