sql server jdbc query on datetime column

Question

I've looked around and could not seem to find this asked specifically, on SO, but I've found similar questions like this one and lots of questions regarding SQL itself or C#...

Here is what I am seeing:

MapSqlParameterSource parameterSource = new MapSqlParameterSource(); //parameterSource.addValue( "insertDate", DateTime.now().minusHours( 1 ).toGregorianCalendar(), Types.TIME ); parameterSource.addValue( "insertDate", new Timestamp( DateTime.now().minusHours( 1 ).getMillis() ) ); List< String > contents = _simpleJdbcTemplate .query( "SELECT TOP (200) inserteddatetime, Contents FROM dbo.tsomeTable WHERE (ServiceName = 'something') and inserteddatetime > :insertDate", new RowMapper< String >() { @Override public String mapRow( final ResultSet rs, final int rowNum ) throws SQLException { System.out.println( rs.getTimestamp( "inserteddatetime" ) ); return rs.getString( "Contents" ); } }, parameterSource );

The query "hangs"\does nothing\never returns if:

I use the uncommented Timestamp object (as presented above)
I replace the parameterSource object with DateTime.now().minusHours( 1 ).toGregorianCalendar() or DateTime.now().minusHours( 1 ).toGregorianCalendar().getTime()
I try the commented out line, but change the type to Timestamp

So, here is my question or questions...

Is there a known bug\issue with querying on datetime columns in sql server?

Why do I have to use Time and not Timestamp?

I suspect that Spring is converting the date objects over to Timestamp when I query with the object directly (as demonstrated in #2).

I've developed several applications backed by SQLServer 2005/2008 and date/time queries/columns work fine. It will be helpful to include the ddl you used to create the table (for the column type of insertedDatetime). — philwb
– philwb, Commented Sep 6, 2012 at 14:35
The hanging/never returning sounds suspect to me. Can you run a Sql Server trace and see what it is doing? msdn.microsoft.com/en-us/library/ms175047.aspx Also, can you confirm that you are using a datetime column with insertDate and not a TIMESTAMP? — pd40
– pd40, Commented Sep 9, 2012 at 13:09

Sahil Mahajan Mj · Accepted Answer · 2012-09-06 08:43:43Z

TIMESTAMP is a server-generated value used to help with data consistency, its not a simple data type, so for storing datetime value, avoid TIMESTAMP datatype.
Also, SQL Server TIMESTAMP is confusing, and it's deprecated. It is replaced by the ROWVERSION keyword, which should reduce confusion.

Collectives™ on Stack Overflow

sql server jdbc query on datetime column

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related