CODEWITHSUNDEEP
sqlsql-server
Irshad
Irshad

Reputation: 303

How to compare time in sql server 2005

I have two columns in table as in a string format starttime and endtime in (HH:MM tt) format like Starttime = 12:15PM and endtime = 12:52PM I want's to add condition in where clause for sql query to check if startime is greater than endtime

Like below.

Select * from table where StartTime > EndTime

(I am not able to compare this condition here)

Can any one help me to add write where for comparing time.

Note: Both the values are stored in string format.

Upvotes: 0

Views: 12395

Answers (5)

Santhosh
Santhosh

Reputation: 1791

Convert the string values to timestamp or date time format and then use your where condition.

--Edited

For SQL Server,

Select * from table where CONVERT(datetime, StartTime , 120) > CONVERT(datetime, EndTime , 120)

Upvotes: 1

deotech
deotech

Reputation: 21

Try this.

SELECT * FROM table WHERE CONVERT (TIME, StartTime) > CONVERT (TIME, EndTime)

For SQL 2005:

SELECT * FROM table WHERE CONVERT(Varchar(8), CONVERT(DATETIME, StartTime), 8) > CONVERT(Varchar(8), CONVERT(DATETIME, EndTime), 8)

Upvotes: 1

Harshil
Harshil

Reputation: 411

Try this

Select * from table where CAST(starttime AS TIME) > CAST(endtime AS TIME)

or

try this 

 Select * from table where CAST( '2013/01/01 ' + starttime AS DATETIME) > CAST( '2013/01/01 ' + Endtime AS DATETIME)

Upvotes: 0

Vishal Suthar
Vishal Suthar

Reputation: 17183

You need to CAST it to TIME

Select * from tbl where CAST(StartTime as TIME) > CAST(EndTime as TIME)

Live Demo

Upvotes: 0

Magnus
Magnus

Reputation: 46909

You need to cast the string values to Time before you do the comparison:

Select * from table where CAST(StartTime As Time) > CAST(EndTime As Time)

The best solution however is to change the data type of the StartTime and EndTime columns to data type Time rather than string

Upvotes: 1

Related Questions