SQL Time Issue

Hey there,

i've just noticed a big problem in my SQL table.

inside the table we have the following fields:

tbl_Arrivals
iId - int identity eg: 1
dArrivalDate - smalldatetime eg: 01/01/2007
sArrivalSchdeduledTime - nvarchar(5) eg: 23:35
sArrivalActualTime - nvarchar(5) eg: 00:15


I have to do a report in my table to establish the length of mins each arrival was late.

so basically sArrivalActualTime - sArrivalSchdeduledTime

which i was doing using the following SQL
DATEDIFF(mi, CAST(sArrivalActualTime AS DateTime),
CAST(sArrivalSchdeduledTime AS DateTime))

now this works fine when the time doesn't go past 23:59.
but for the example data above it will cause serious issues.

has anyone come across a solution to this before.

Wouldn't you need a field for the actual arrival date (as in you example it was supposed to arrive on Monday at 23:35 but actually arrived on Tuesday at 00:05. They are two different dates.

You might want to consider storing the date and time together. You could have

ScheduleArrival
ActualArrival

both as date times. It will make your datetime manipulation much easier!

I agree with The_Duck.
An arrival is always on a date and time, so you should change your design to store the scheduled and actual arrival as datetime or smalldatetime, depending on the resolution you need. I would guess smalldatetime should do it for you.

Thanks guys for the replies... the more i thought about it the more i came to the conclusion that it couldn't be done.
I just said i'd post in hope.

At the beginning of the project i didn't expect them to be wanting to comparing the times i was storing as strings.

The way the application i have was just one date and then drop downs for hours and minutes. hmmmm i will have to make some changes