Datetime Query Question - 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)

Datetime Query Question

Author

Topic

dvadams
Starting Member

4 Posts

Posted - 2002-12-02 : 14:41:37

Hello,

I'm somewhat of a newbie to SQL and am stumped on how to perform a particular datetime query. I'm trying to query a Datetime column by a specific date and then by a specific time range (ex. 06:00:00-10:00:00).

With the following code I'm able to narrow down a date range, however this does not bring back a specific time range. Any thoughts on how I can search a datetime column using a specific time range? Any help would be appreciated.

Here's the code I've been using to query by date range:

SELECT HX_EVENTS.EVENTID, HX_EVENTS.OBJECTNAME, HX_ENTRIES.EVENTTIME, HX_AUXDATA.ITEMDATA
FROM NT3400.dbo.HX_AUXDATA HX_AUXDATA, NT3400.dbo.HX_ENTRIES HX_ENTRIES, NT3400.dbo.HX_EVENTS HX_EVENTS
WHERE HX_AUXDATA.ENTRYID = HX_ENTRIES.ENTRYID AND HX_AUXDATA.ENTRYID = HX_EVENTS.ENTRYID AND HX_AUXDATA.OBJSITE = HX_ENTRIES.OBJSITE AND HX_AUXDATA.OBJSITE = HX_EVENTS.OBJSITE AND HX_ENTRIES.ENTRYID = HX_EVENTS.ENTRYID AND HX_ENTRIES.OBJSITE = HX_EVENTS.OBJSITE AND ((HX_ENTRIES.EVENTTIME>={ts '2002-10-28 23:33:31'} And HX_ENTRIES.EVENTTIME<={ts '2002-11-05 19:55:24'}) AND (HX_AUXDATA.ITEMDATA='LAB Cafe Cashier #1'))

baldeep
Starting Member

18 Posts

Posted - 2002-12-02 : 14:55:47

You should be able to query based on the datetime. Try using BETWEEN?


DROP TABLE DateTable
GO

CREATE TABLE DateTable (
	Id INT IDENTITY(1, 1) PRIMARY KEY,
	RandomDate DATETIME NULL)
GO

DECLARE @ctr INT
SET @ctr = 1000

WHILE @ctr > 0 --Here we generate 1000 dates centered about the current time.
BEGIN
	INSERT INTO DateTable VALUES (DATEADD(mi, (0.5 - RAND())*48*60, GETDATE()))
	SET @ctr = @ctr - 1
END
GO

--If everything went according to plan this should give about 500 records.
SELECT * FROM DateTable WHERE RandomDate BETWEEN DATEADD(mi, -12*60, GETDATE()) AND DATEADD(mi, 12*60, GETDATE())
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources