Time in sqlserver - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Time in sqlserver

Author

Topic

anupalavila
Yak Posting Veteran

56 Posts

Posted - 2008-09-27 : 02:13:31

Hai,

I am with a C#.net sqlserver2000 attendance marking application, In the application first I am trying to add shifts like dayshift, nightshift etc with shift starting time and shift ending time. I am trying like this

insert into Shiftsmaster(ShiftName,StartTime,EndTime) values ('Evening','04:00 PM','01:00 AM')

this shift when I calculate the time interval using

SELECT DATEDIFF(Hour, '1900-01-01 16:00:00.000','1900-01-01 01:00:00.000')

its -15 but it should be 8

how can I enter correct shift time and get the shift interval

Thanks in advance
Anu Palavila

Thanks and Regards
Anu Palavila

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-27 : 02:27:59

thats because you're using datetime field for storing time alone. SQL server by default stores both date & time and if you dont pass datepart it will assume default date of 1 jan 1900 which is causing above confusion.
the best thing is to store date also along with time so that it reads for example

insert into Shiftsmaster(ShiftName,StartTime,EndTime) values ('Evening','26 Sep 2008 04:00 PM','27 Sep 2008 01:00 AM')
and
SELECT DATEDIFF(Hour, '2008-09-26 16:00:00.000','2008-09-27 01:00:00.000') gives
correct hour difference

anupalavila
Yak Posting Veteran

56 Posts

Posted - 2008-09-27 : 02:38:16

but shift doesn't have any date its general only with a time interval later when this shift is allocated to an employee and his login time is marked the current date and time is entered, but here we want only the time to be entered

Thanks and Regards
Anu Palavila

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-27 : 02:41:05

quote:
Originally posted by anupalavila

but shift doesn't have any date its general only with a time interval later when this shift is allocated to an employee and his login time is marked the current date and time is entered, but here we want only the time to be entered

Thanks and Regards
Anu Palavila

They you may have to use numeric or decimal fields for storing the time. but again time comparisons will be a problem.
can i assume that you're using sql 2000 as i've posted it in 2000 forum?

anupalavila
Yak Posting Veteran

56 Posts

Posted - 2008-09-27 : 02:55:39

ya I am with with sql 2000, is there any method to calculate only difference between two times with out looking the date

Thanks and Regards
Anu Palavila

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-27 : 03:33:38

see if this helps

http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server
http://weblogs.sqlteam.com/jeffs/archive/2004/12/02/2959.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources