Comparing Time in SP - 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

Transact-SQL (2000)

Comparing Time in SP

Author

Topic

t1g312
Posting Yak Master

148 Posts

Posted - 2004-07-21 : 05:50:42

Hi all,

I have a text field in my frontend VB app which accepts time in the format "hh:mm". I need to compare this time with a time range in my stored procedure. When I pass this time to my stored procedure as "adDate" parameter, it gets converted to "hh:mm:ss PM". The comparison doesn't seem to work and I don't know where I am going wrong. Please help!

VB:

objCmd.Parameters.Append objCmd.CreateParameter("@FlightDate", adDate, adParamInput, , txtFltDate.Text)
objCmd.Parameters.Append objCmd.CreateParameter("@FlightTime", adDate, adParamInput, , txtFltTime.Text)
objCmd.Parameters.Append objCmd.CreateParameter("@Error", adInteger, adParamOutput)

SP:

CREATE PROCEDURE xsp_SP
@FlightDate datetime,
@FlightTime datetime,
@ErrorCode int OUT
AS
SET NOCOUNT ON
if @FlightTime between '6:00:00 AM' and '4:00:00 PM'
SELECT Morning FROM table WHERE ADate = @FlightDate
SELECT @ErrorCode = @@Error
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Thanks in advance!

Adi

-------------------------
/me sux @sql server

SamC
White Water Yakist

3467 Posts

Posted - 2004-07-21 : 06:48:12

In SQL, DATETIME contains both date and time. Passing the date and time separately as individual DATETIME parameters will result in values like:

@FlightDate = 'Jul 21 2004 12:00AM' -- Time of midnight added by SQL
@FlightTime = 'Jan 1 1900 4:32PM' -- Date of Jan 1 1900 added by SQL

You'll need to consider this in your comparisons, or rewrite your code to check a single DATETIME.

Here's one way to do it:

SELECT Morning from Table
-- Assuming both are DATETIME, both better be midnight to pass this comparison
WHERE Adate = @FlightDate
AND DATEPART(hh, @FlightTime) BETWEEN 4 AND 6 -- Compare the hours only, no date

HTH

Sam

t1g312
Posting Yak Master

148 Posts

Posted - 2004-07-21 : 07:00:56

Hi SamC,

Thanks for the reply. Can I compare time in the form hh:mm and not just the hour?

Adi

-------------------------
/me sux @sql server

SamC
White Water Yakist

3467 Posts

Posted - 2004-07-21 : 07:09:24

In a variety of ways. Lookup CONVERT and DATEPART in Books OnLine (BOL). I'd suggest a solution, but I really don't understand the range of values for Adate, the FlightDate and FlightTime. What's with the hardcoded 4 and 6AM? Give some more info on what you're trying to do.

If Adate has both date and time, then why not pass @Time1 and @Time2 as DATETIME parameters and simplify your query using:

SELECT morning FROM table
WHERE Adate BETWEEN @Time1 and @Time2 -- Compare down to the minute

t1g312
Posting Yak Master

148 Posts

Posted - 2004-07-21 : 07:15:25

The problem with your code is that when I try to compare time between 10 in the night and 6 in the morning, the statement would be "between 22 and 06" which doesn't make sense!

Adi

-------------------------
/me sux @sql server

t1g312
Posting Yak Master

148 Posts

Posted - 2004-07-21 : 07:25:27

Basically I am trying to create a schedule staff working on shifts. There are 3 shifts - 0600-1600 (morning), 1600-0001 (AN) and 2200-0600 (night). When the date and time is selected, I need to select the employee in that particular shift.

Here is the entire code:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE xsp_GetAPCByFltDate
@FlightDate datetime,
@FlightTime datetime,
@ErrorCode int OUT
AS
SET NOCOUNT ON
if datepart(hh,@FlightTime) between 06 and 16
SELECT Morning FROM table WHERE ADate = @FlightDate
else
if datepart(hh,@FlightTime) between 17 and 24
SELECT Afternoon FROM table WHERE ADate = @FlightDate
else
if datepart(hh,@FlightTime) between 22 and 06
SELECT Night FROM table WHERE ADate = @FlightDate
SELECT @ErrorCode = @@Error
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Adi

-------------------------
/me sux @sql server

t1g312
Posting Yak Master

148 Posts

Posted - 2004-07-21 : 07:36:17

And the table structure is:

ADate datetime
Morning varchar
Afternoon
Night varchar

where morning afternoon and night have the employee code.

Adi

-------------------------
/me sux @sql server

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-21 : 07:51:21

[code]
CREATE PROCEDURE xsp_GetAPCByFltDate
@FlightDate datetime,
@FlightTime datetime,
@ErrorCode int OUT
AS
SET NOCOUNT ON
if datepart(hh,@FlightTime) between 06 and 16
SELECT Morning FROM table WHERE ADate = @FlightDate
else
if datepart(hh,@FlightTime) between 17 and 21
SELECT Afternoon FROM table WHERE ADate = @FlightDate
else
if datepart(hh,@FlightTime) >= 22 OR datepart(hh,@FlightTime) <= 6
SELECT Night FROM table WHERE ADate = @FlightDate
SELECT @ErrorCode = @@Error
[/code]

t1g312
Posting Yak Master

148 Posts

Posted - 2004-07-21 : 08:17:52

Works brilliantly!! Thanks JasonGoff!!!

I think I need to add /me sux at logic too to my signature!

Adi

-------------------------
/me sux @sql server

t1g312
Posting Yak Master

148 Posts

Posted - 2004-07-21 : 09:00:41

One more question. The above works fine because I am only comparing the hour. What if I want to compare the time with "hh:mm"?

Adi

-------------------------
/me sux @sql server

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources