SQL Time Issue

[Strider]

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.

[GaryMazzone]

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.

[The_Duck]

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!

[kaffenils]

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.

[Strider]

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