Query Help. - 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 2008 Forums

Transact-SQL (2008)

Query Help.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2014-04-18 : 17:06:06

I need to query data from "03/30/2014 2:00 A.M. through 04/01/2014 4:00 P.M.". The real sp in production is not return correct data
and how can I get the result below. I have over 6 millions rows in production table and is there an easy way to filter out only
certain date and time range without missing any rows. SQL 2012.

Thank you in advance.

IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
DROP TABLE #Temp
GO

CREATE TABLE #Temp
(
objie INT NULL,
eventdate DATETIME NULL,
)
GO

INSERT #Temp VALUES (30620, '2014-03-29 06:00:00.000'),
(357087, '2014-03-31 02:01:54.000'),
(357081, '2014-03-31 01:59:48.000'),
(357740, '2014-03-31 04:02:01.000'),
(359448, '2014-04-01 16:00:01.000'),
(359456, '2014-04-01 19:23:48.000');
GO

eventdate eventdate1 time
----------- ------------------- ------
30620 2014-03-29 06:00:00 6:00AM
357087 2014-03-31 02:01:54 2:01AM *
357081 2014-03-31 01:59:48 1:59AM
357740 2014-03-31 04:02:01 4:02AM *
359448 2014-04-01 16:00:01 4:00PM *
359456 2014-04-01 19:23:48 7:23PM

-- Result want:
eventdate eventdate1 time
----------- ------------------- ------
357087 2014-03-31 02:01:54 2:01AM
357740 2014-03-31 04:02:01 4:02AM
359448 2014-04-01 16:00:01 4:00PM

--Testing... need a better way to pass in date and time range.

SELECT objie
eventdate,
CONVERT(CHAR(19),eventdate, 120) AS 'eventdate1',
RIGHT(CAST(eventdate AS DATETIME), 6) AS 'time'
FROM #Temp
WHERE CONVERT(CHAR(19),eventdate, 120) > '2014-03-31 02:00:00' AND CONVERT(CHAR(19),eventdate, 120) <= '2014-04-01 16:01:00'
GO

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-18 : 17:12:17

WHERE eventdate > '20140331 02:00:00' AND eventdate < '20140401 16:02:00'

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-04-18 : 17:52:41

Not to be completely pedantic but...[CODE]WHERE eventdate BETWEEN '20140331 02:00:00' AND '20140401 16:02:00'
[/CODE]Would include the endpoints and the BETWEEN operator is usually faster.

(Okay, maybe not completely pedantic...but its close!)

===============================================================================
“Everyone wants a better life: very few of us want to be better people.”
-Alain de Botton

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-18 : 18:04:24

quote:
Originally posted by Bustaz Kool

the BETWEEN operator is usually faster.

Do you mean faster to type?

Cuz, they are the same thing.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-19 : 01:02:55

It certainly won't execute any faster.

And good coding technique when dealing with date/datetimes is to use >/>= and < rather than BETWEEN.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources