jpa date nativequery sql server

Question

I'm developing something using jpa and sql server 2008 and I'm getting the following error

Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string.
Error Code: 241

Call: SELECT TOP 100 * FROM [database].[dbo].[registro] WHERE (indate between 'Wed Apr 30 01:01:01 COT 2014' and 'Mon May 12 01:01:01 COT 2014') or name like '%and%'
Query: ReadAllQuery(referenceClass=Register sql="SELECT TOP 100 * FROM [database].[dbo].[register] WHERE (indate between 'Wed Apr 30 01:01:01 COT 2014' and 'Mon May 12 01:01:01 COT 2014') or name like '%and%'")

the jpa query:

Query query = em.createNativeQuery("SELECT TOP 100 * FROM [database].[dbo].[register] WHERE (indate between '"+idate+"' and '"+fdate+"') or name like '%"+name+"%'",Register.class);

Can somebody please tell me what to change or a better way to do the query.

Answer 1

You should't use date string in native query.

Also you must avoid sql injection by using setParemeter.

The following query should work :

String query = "SELECT TOP 100 * FROM [database].[dbo].[register] WHERE (indate between ? and  ?);"

Query query = em.createNativeQuery(query).setParameter(1,[your-date- value],TemporalType.DATE).setParameter(2,[your second-date-value],TemporalType.DATE);

List<Register> registerList = query.getResultList();

I hope this help you.