Finding just the Time for a Between clause

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)

Finding just the Time for a Between clause

Author

Topic

HardCode
Starting Member

8 Posts

Posted - 2004-10-28 : 10:42:49

I have a DATETIME field called Completed. I am trying to query all records where the time portion of the DATETIME is BETWEEN '00:00:00' and '08:00:00'. I will then nest this SQL into an IF so I can check if the record was completed either BETWEEN midnight and 8 am, or, BETWEEN 8:00:01 and 4 pm, or, BETWEEN 16:00:01 and 23:59:59 at night. I just can't seem to figure out or find how to even SELECT a datetime field where just the time part is BETWEEN '00:00:00' AND '08:00:00'.

I looked at lots of datetime functions but I can't seem to find anything to isolate the time. I even tried DATEPART, but that only returns one element of the time, not the whole time. I even tried to convert the DATEPARTs to VarChar, and then concatenate them, but that didn't work at all. Can anyone help? What is the right way to do this?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-28 : 10:48:57

have you tried
where Convert(varchar(8), columnName, 108) between @val1 and @val2

Convert(varchar(8), columnName, 108) gives just the time part of datetime

Go with the flow & have fun! Else fight the flow

HardCode
Starting Member

8 Posts

Posted - 2004-10-28 : 11:19:17

Okay, that works fine when I run this SQL:


SELECT Completed 
FROM CTS_Invites 
WHERE CONVERT(varchar(8), Completed, 108) BETWEEN '00:00:00' AND '08:00:00'

However, it won't work here:


SELECT 	CTS_Invites.*,
	TimeSlot = 
	CASE CONVERT(varchar(8), Completed, 108)
		WHEN BETWEEN '00:00:00' AND '08:00:00' THEN '1st Shift'
		WHEN BETWEEN '08:00:01' AND '16:00:00' THEN '2nd Shift'
		WHEN BETWEEN '16:00:01' AND '23:59:59' THEN '3rd Shift'
		ELSE 'Error'
	END
FROM	CTS_Invites

The BETWEEN should evaluate to TRU or FALSE, and according to Books Online, that should satisfy the CASE's WHEN clause. Can you see what I am doing wrong? The error is Server: Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'BETWEEN'.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-28 : 11:40:52

it does evaluate to 1 or 0 but not on its own it must be accompanied by column name which is in your case convert(...)


CASE 
	WHEN CONVERT(varchar(8), Completed, 108) BETWEEN '00:00:00' AND '08:00:00' THEN '1st Shift'
	WHEN CONVERT(varchar(8), Completed, 108) BETWEEN '08:00:01' AND '16:00:00' THEN '2nd Shift'
	WHEN CONVERT(varchar(8), Completed, 108) BETWEEN '16:00:01' AND '23:59:59' THEN '3rd Shift'
	ELSE 'Error'
	END

Go with the flow & have fun! Else fight the flow

HardCode
Starting Member

8 Posts

Posted - 2004-10-28 : 12:02:59

Works perfectly now. Thanks much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources