0

I am trying to compare time through hibernate in SQL Server 2008.

The following code returns this error : The data types time and datetime are incompatible in the less than or equal to operator.

crit = session.createCriteria(ObdBlackoutHours.class);
Criterion start = Restrictions.le("blackoutStart", new Date());
        Criterion end = Restrictions.gt("blackoutEnd",new Date());
        List list = crit.add(Restrictions.conjunction().add(start).add(end))
                .list();
        if(list.isEmpty())
            return false;
        else 
            return true;

The table design is the following:

CREATE TABLE [dbo].[obd_blackout_hours](
[id] [int] NOT NULL,
[blackout_end] [time](7) NOT NULL,
[blackout_start] [time](7) NOT NULL)

I understand that the db contains only 10:17:37 and what I am passing is something like this Thu Nov 14 10:17:37 IST 2013 which it is unable to compare. I tested the same code in mysql which seems to be working very fine. But SQL Server 2008 is creating the problem. I also tried passing

currentDate = new SimpleDateFormat("HH:mm:ss").parse(new SimpleDateFormat("HH:mm:ss").format(Calendar.getInstance().getTime()));

and

new ObdBlackoutHours(1,new Date(),new Date()).getBlackoutStart()

instead of the just the Date() object. This also fails. How should I compare time and get results.

The following is the entity class

@Entity
@Table(name = "obd_blackout_hours", schema = "dbo", catalog = "IVR_Data")
public class ObdBlackoutHours implements java.io.Serializable {

private int id;
private Date blackoutStart;
private Date blackoutEnd;
private Set<Service> services = new HashSet<Service>(0);

public ObdBlackoutHours() {
}

public ObdBlackoutHours(int id, Date blackoutStart, Date blackoutEnd) {
    this.id = id;
    this.blackoutStart = blackoutStart;
    this.blackoutEnd = blackoutEnd;
}

public ObdBlackoutHours(int id, Date blackoutStart, Date blackoutEnd,
        Set<Service> services) {
    this.id = id;
    this.blackoutStart = blackoutStart;
    this.blackoutEnd = blackoutEnd;
    this.services = services;
}

@Id
@Column(name = "id", unique = true, nullable = false)
public int getId() {
    return this.id;
}

public void setId(int id) {
    this.id = id;
}

@Temporal(TemporalType.TIME)
@Column(name = "blackout_start", nullable = false, length = 16)
public Date getBlackoutStart() {
    return this.blackoutStart;
}

public void setBlackoutStart(Date blackoutStart) {
    this.blackoutStart = blackoutStart;
}

@Temporal(TemporalType.TIME)
@Column(name = "blackout_end", nullable = false, length = 16)
public Date getBlackoutEnd() {
    return this.blackoutEnd;
}

public void setBlackoutEnd(Date blackoutEnd) {
    this.blackoutEnd = blackoutEnd;
}

@OneToMany(fetch = FetchType.LAZY, mappedBy = "obdBlackoutHours")
public Set<Service> getServices() {
    return this.services;
}

public void setServices(Set<Service> services) {
    this.services = services;
}

}
3
  • java.sql.Date or java.util.Date? Commented Nov 14, 2013 at 5:28
  • java.util.Date. But it gives the same error with new java.sql.Time(new java.util.Date().getTime()) also. i.e. con not compare time and datetime Commented Nov 14, 2013 at 5:28
  • nobody has ever compared time in sql server??? Commented Nov 14, 2013 at 5:41

1 Answer 1

1

Refer to the following blog:
https://techcommunity.microsoft.com/t5/sql-server-blog/using-time-and-date-data-types-part-1-it-s-about-time/ba-p/383611

Need to add the following to your hibernate connection url string
I am not sure if it's true/false just play with it.
sendTimeAsDateTime=false

Sign up to request clarification or add additional context in comments.

1 Comment

In my case even I use sendTimeAsDateTime=false in my connection url also not working.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.