Time Format - 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 Format

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-15 : 08:43:46

Palm01 writes "Hi
I wonder if someone could help, i need a good way of comparing time fields to find the greater one, it would be easier, i am sure, if i could convert it to a 24 hour time format

please if someone could help ....

e.g timefield < or > timefield"

lfmn
Posting Yak Master

141 Posts

Posted - 2002-02-15 : 14:09:16

declare @time1 datetime, @time2 datetime
declare @sec1 int, @sec2 int
set @time1 = '2002-02-15 11:53:45.167'
set @time2 = '2002-02-15 12:53:45.167'

select @sec1 = datepart(hh,@time1)*3600 + datepart(mi,@time1)*60 + datepart(ss,@time1)
select @sec2 = datepart(hh,@time2)*3600 + datepart(mi,@time2)*60 + datepart(ss,@time2)

select @sec1, @sec2

if (@sec1 > @sec2)
begin
select 'time1 is greater'
end
else
begin
select 'time2 is greater'
end

cursors are like hammers - sometimes you have to use them, but watch your thumb!

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-02-15 : 17:34:23

How about using Convert(varchar(12), TimeField, 114)?

Here's some test data:


CREATE TABLE #TimeList (
	TimeID	int	NOT NULL	IDENTITY,
	StartTime	datetime	NULL,
	EndTime		datetime	NULL,
)
GO
SET NOCOUNT ON
INSERT INTO #TimeList (StartTime, EndTime) VALUES (getdate(), getdate() + 0.5)
INSERT INTO #TimeList (StartTime, EndTime) VALUES (getdate(), '23:55:00')
INSERT INTO #TimeList (StartTime, EndTime) VALUES (getdate(), '1:30:55')
INSERT INTO #TimeList (StartTime, EndTime) VALUES (getdate(), '2/20/2002 09:00:00')
SET NOCOUNT OFF
GO

SELECT TimeID, Convert(varchar(12), StartTime, 114) ConvertStart, Convert(varchar(12), EndTime, 114) ConvertEnd, CASE
	WHEN Convert(varchar(12), StartTime, 114) > Convert(varchar(12), EndTime, 114) THEN 'Start'
	WHEN Convert(varchar(12), StartTime, 114) < Convert(varchar(12), EndTime, 114) THEN 'End'
	ELSE 'TIED'
	END AS GreaterTime
FROM #TimeList

GO
Drop Table #TimeList

------------------------
GENERAL-ly speaking...

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-16 : 07:52:24

Or this sort of thing:

DATEADD(day, DATEDIFF(day, @time1, @time2), @time1) > @time2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources