timetable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

timetable

Author

Topic

blackbox320
Starting Member

10 Posts

Posted - 2007-06-08 : 01:32:48

I am building a sql table. It is a time table. It has fields From/To stations, departure_time/arrival_time, blocktime and total time. Is there any way I can create a computed column which subtracts departure_time from arrival_time and displays it in block_time column. Is there any way to sum the block_time. Datetime is unable to sum time if it is above 24:00. I am using SQL 2000

vishwamukh

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-08 : 01:45:02

Yes, you can create computed column for block_time and use datediff to set value. The data type of block_time should be int, so you can sum it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-08 : 01:45:08

Can you post your table DDL, sample data and the result that you want ?

basically you can use datediff() to find the different in time.

blackbox320
Starting Member

10 Posts

Posted - 2007-06-08 : 01:52:20

quote:
Originally posted by khtan

Can you post your table DDL, sample data and the result that you want ?

basically you can use datediff() to find the different in time.

KH

The table is created using enterprise manager wizard. The columns are:

FltNo varchar(10) NULL, /* SQ 123 */
Frequency varchar(8) NULL, /* 1234567 1 is for Monday */
From varchar(50) NULL, /* Singapore */
To varchar(50) NULL, /* New Delhi */
Departure_Time smalldatetime(4) NULL, /* 08:00 */
Arrival_Time smalldatetime(4) NULL, /* 11:00 */
Block_Time smalldatetime(4) NULL, /* 05:30 "2:30" time diff*/
Using datediff(time,Arrival_Time - Departure_Time) in column-> formula properties doesnt work.

vishwamukh

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-08 : 01:55:33

So?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-08 : 06:25:18

[code]
drop table #temp
create table #temp
(
FltNo varchar(10) NULL, /* SQ 123 */
Frequency varchar(8) NULL, /* 1234567 1 is for Monday */
[From] varchar(50) NULL, /* Singapore */
[To] varchar(50) NULL, /* New Delhi */
Departure_Time smalldatetime NULL, /* 08:00 */
Arrival_Time smalldatetime NULL, /* 11:00 */
Block_Time as case when Arrival_Time > Departure_Time
then datediff(minute, Departure_Time, Arrival_Time)
else datediff(minute, Departure_Time, dateadd(day, 1, Arrival_Time))
end
)

insert into #temp
select 'SQ 123', '1234567', 'Singapore', 'New Delhi', '08:00', '11:00' union all
select 'SQ 123', '1234567', 'Singapore', 'New Delhi', '08:00', '01:00'

select * from #temp

[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-08 : 09:42:47

Also instead of having computed column, you can derive it in the select statement

Madhivanan

Failing to plan is Planning to fail

blackbox320
Starting Member

10 Posts

Posted - 2007-06-10 : 13:17:49

quote:
Originally posted by khtan

drop table #temp
create table #temp
(
FltNo varchar(10) NULL, /* SQ 123 */
Frequency varchar(8) NULL, /* 1234567 1 is for Monday */
[From] varchar(50) NULL, /* Singapore */
[To] varchar(50) NULL, /* New Delhi */
Departure_Time smalldatetime NULL, /* 08:00 */
Arrival_Time smalldatetime NULL, /* 11:00 */
Block_Time as case when Arrival_Time > Departure_Time
then datediff(minute, Departure_Time, Arrival_Time)
else datediff(minute, Departure_Time, dateadd(day, 1, Arrival_Time))
end
)

insert into #temp
select 'SQ 123', '1234567', 'Singapore', 'New Delhi', '08:00', '11:00' union all
select 'SQ 123', '1234567', 'Singapore', 'New Delhi', '08:00', '01:00'

select * from #temp

Thanks a lot for your solution.

SQ 123 1234567 Singapore New Delhi
1900-01-01 08:00:00 1900-01-01 11:00:00 180

As you can see, the time column is showing date and time. Is there any way, so that, only time is shown in the column as "08:00" and not "08:00:00"

Thanking you once again
vishwa mukh

KH

vishwamukh

blackbox320
Starting Member

10 Posts

Posted - 2007-06-10 : 13:24:24

quote:
Originally posted by madhivanan

Also instead of having computed column, you can derive it in the select statement

Madhivanan

Failing to plan is Planning to fail

Dear sir,
Thanks for your reply. The purpose is to get the job done in most simple way. Maintenance is simple and easy. Solution provided by KHTAN is efficient, simple and easy to maintain. Once again thanks for your interest.

Thanking you,
vishwa mukh

vishwamukh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-10 : 22:41:58

quote:
As you can see, the time column is showing date and time. Is there any way, so that, only time is shown in the column as "08:00" and not "08:00:00"

Yes. Do the formating in your front end application. Or use convert() to do the job in SQL.

blackbox320
Starting Member

10 Posts

Posted - 2007-06-11 : 01:10:48

Itried the convert() funtion.
insert into temp
select 'SQ 123', '1234567', 'Singapore', 'New Delhi', '08:00', '11:00' union all
select 'SQ 123', '1234567', 'Singapore', 'New Delhi', '08:00', '01:00'

select convert(smalldatetime, Departure_Time,108)
from temp
go

select * from temp

It didnot produce the desired output.

While trying to enter data directly into the table using the enterprise manager genarated errors in the block time column.

Formatting the departure time column using "{0:c}" in visual web developer 2005 did not produce the desired result.

Thanking you,

Vishwa mukh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-11 : 01:13:42

[code]
select convert(varchar(5), Departure_Time, 108)
[/code]

blackbox320
Starting Member

10 Posts

Posted - 2007-06-11 : 23:31:05

quote:
Originally posted by khtan

select convert(varchar(5), Departure_Time, 108)

KH

Thanks a lot. Your solution worked.

vishwamukh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources