CODEWITHSUNDEEP
sqlsql-server-2012
Brooklyn99
Brooklyn99

Reputation: 1047

How to construct a varchar column in 'HH:mm' to datetime format in SQL Server?

I have a table Bus with one column busStopTime in the table as a varchar field which holds the time in 'HH:mm' format. I am trying to convert it to a datetime so that I can compare with other datetime field.

But I am not able to convert it.

Example: BUS

busName route   busStopTime busStartTime tripNumber
----------------------------------------------------------

690      ABC      03:30        04:30        1

690      ABC      05:30        06:30        2

690      ABC      07:30        08:30        3

Here I have three rows as a example with same busNames but different timings.I wanted to write a query so on a given time (in DD-MM-YYYY HH:mm:ss:SSS)I can know the trip number for that particular time using the busStopTime and busStartime columns.

I have no other code with me to share but hoping I would get some clue on how to convert it using SQL query?

Upvotes: 0

Views: 435

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270361

You can create a datetime using addition. For the time on the current date:

select (convert(datetime, convert(date, getdate())) +
        convert(datetime, busStartTime)
       ) today_busStartTime

Upvotes: 1

Andreas
Andreas

Reputation: 159135

In java, using JDBC, get the string value and parse it to a LocalTime (added in Java 8), which has methods for easy comparison to other LocalTime objects, and for manipulating the time value, e.g. adding 15 minutes to it.

LocalTime time = LocalTime.parse(rs.getString("busStopTime"));

If using Java 6 or 7, use the ThreeTen Backport library.

Upvotes: 0

Related Questions