[ Resolved! ] Matching DateTime ...? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

[ Resolved! ] Matching DateTime ...?

Author

Topic

DTFan
Yak Posting Veteran

52 Posts

Posted - 2008-06-17 : 11:21:09

I hope this is something silly that I'm missing. After some searching I found that the
Convert(varchar,datetime,type) for looking at the time part of a DateTime field is 108
(for "hh:mm:ss").

Here's what I've got. In the datatable, the field is a datetime type. It is stored like
this: 8/30/2007 8:41:41 AM

Some I want to get records for specific employees for a certain date but not return any
records if the time is 00:00:00.

So here is what I've got so far:


    -- parameter passed to the stored procedure
    @in_dtDateFrom    DATETIME

BEGIN

    SELECT	ocID, locID, oDateTime
    FROM	tblOConn
    WHERE	CONVERT(DATETIME, CONVERT(VARCHAR(11), tblOConn.oDateTime, 101)) =
                    @in_dtDateFrom
    AND		CONVERT(VARCHAR(8), tblOConn.oDateTime, 108) <> '00:00:00'
    AND		empID = @in_intEmpID

END

This returns the appropriate records from the query but it doesn't exclude the times that
are 00:00:00. Can anyone help me on this? I *hope* it's something silly that I'm missing
or doing wrong.

Thank you in advance for any and all help/suggestions/etc that you can give. It is very
much appreciated.

DTFan
Ever-hopeful programmer-in-training

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-17 : 11:43:28

Try


SELECT	ocID, locID, oDateTime
    FROM	tblOConn
    WHERE	tblOConn.oDateTime-dateadd(day,datediff(day,0,tblOConn.oDateTime),0)<>
                 @in_dtDateFrom-dateadd(day,datediff(day,0,@in_dtDateFrom),0)   
    AND		empID = @in_intEmpID

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 11:45:29

[code]
@in_intEmpID INT,
@in_dtDateFrom DATETIME
)
AS

SET NOCOUNT ON

SET @in_dtDateFrom = DATEDIFF(DAY, '19000101', @in_dtDateFrom)

SELECT ocID,
locID,
oDateTime
FROM tblOConn
WHERE oDateTime > @in_dtDateFrom
AND oDateTime < DATEADD(DAY, 1, @in_dtDateFrom)
AND empID = @in_intEmpID[/code]

E 12°55'05.25"
N 56°04'39.16"

DTFan
Yak Posting Veteran

52 Posts

Posted - 2008-06-17 : 11:47:02

madhivanan - Thank you. That did it. Is there a difference (besides being a lot cleaner) in the DateAdd and the Convert functions? Looking at what you have (and again, I'm am sooooooo far from being skilled at SQL) would be faster because it doesn't have to "convert" things?

Anyway, thank you very much for that fast and accurate response. It is very much appreciated.

Peso - sorry, posted response before I saw yours. Thank you also (Very Much Appreciated) :D

DTFan
Ever-hopeful programmer-in-training

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 11:53:04

Try mine too. Cleaner and even faster because you can use any present index over both oDateTime column and empID column.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-17 : 11:55:23

quote:
Originally posted by Peso

Try mine too. Cleaner and even faster because you can use any present index over both oDateTime column and empID column.

E 12°55'05.25"
N 56°04'39.16"

Yes it is. I thought of posting it but misunderstood the question

Madhivanan

Failing to plan is Planning to fail

DTFan
Yak Posting Veteran

52 Posts

Posted - 2008-06-19 : 12:31:17

quote:
Originally posted by Peso

Try mine too. Cleaner and even faster because you can use any present index over both oDateTime column and empID column.

E 12°55'05.25"
N 56°04'39.16"

Yep. I incorporated that and it's running like a champ. Thank you very much for that :D

DTFan
Ever-hopeful programmer-in-training

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources