between times - 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)

between times

Author

Topic

jaystar
Starting Member

12 Posts

Posted - 2012-06-14 : 08:43:38

Hi all hoping somebody can help me, I am trying to make the below query between 0700hrs and 1900hrs but it failing. What am I doing wrong
Jay

SELECT *
FROM SLADB.dbo.DocketTB
WHERE (Docket_Date = CONVERT(DATETIME, '2012-06-13 00:00:00', 102))
AND DATEPART(hh,[Docket_EngFinish]) >= '07' AND DATEPART(hh,[Docket_EngFinish]) <= '19' --AND DATEPART(MINUTE,[Docket_DateRaised]) <= '00'

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 08:53:05

Should work. Is Docket_Date a datetime? Is Docket_EngFinish a datetime with a 0 date (19000101)?
If so then

where Docket_Date + Docket_EngFinish between '2012-06-13 07:00:00' and '2012-06-13 19:00:00'

datepart returns an integer so your '07' and '19' s.b. 7 and 19 - but they will be converted anyway.
DATEPART(hh,[Docket_EngFinish]) between 7 and 19

I suspect you are having a datatype issue.
Have you checked the data to see if this should return anything?
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jaystar
Starting Member

12 Posts

Posted - 2012-06-14 : 09:07:50

Hi that works but I am going to be passing variables and am confused how to pass a date range and time range in this way, this is my date range SQL query.

SELECT Con1, Con10, Con2, Con3, Con4, Con5, Con6, Con7, Con9, Contract, Docket_Category, Docket_Date, Docket_DateRaised, Docket_EngFinish, Docket_EngStart, Docket_EngineerName, Docket_Id, Docket_Machine, Docket_Number, Docket_Status, Docket_SubCategory, Duration, Module, Monitor_Time, Operator_Name, Section, Waittime, spare8 FROM DocketTB WHERE (Docket_Status = 'CL') AND (Docket_Category NOT LIKE '%OPERATOR%') AND (Docket_Category NOT LIKE '%Out Of SLA%') AND (Docket_Category NOT LIKE '%MATERIAL%') AND (Contract = 1) AND (Docket_Date BETWEEN @date1 AND @date2) ORDER BY Docket_Date

jaystar
Starting Member

12 Posts

Posted - 2012-06-14 : 09:30:14

SELECT Con1, Con10, Con2, Con3, Con4, Con5, Con6, Con7, Con9, Contract, Docket_Category, Docket_Date, Docket_DateRaised, Docket_EngFinish, Docket_EngStart, Docket_EngineerName, Docket_Id, Docket_Machine, Docket_Number, Docket_Status, Docket_SubCategory, Duration, Module, Monitor_Time, Operator_Name, Section, Waittime, spare8

FROM DocketTB

WHERE (Docket_Date = @sin1)

AND (DATEPART(hh[Docket_EngFinish]) between @sin3 and @sin4

AND (Docket_Status = 'CL')

My attempt is not working

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 09:40:26

Create a table variable (just the date columns and status), populate it
Declare and set the variables.
Run the query on that
Then post everything so we can run it and see what is happening.

It's almost certainly a data problem - you'll probably spot it when you do the above.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-14 : 09:43:06

It is still unknown to us what your data types are, but in your query you are missing a comma
AND (DATEPART(hh , [Docket_EngFinish]) between @sin3 and @sin4

we could probably improve your query if you told us what the data types are for Docket_Date and Docket_EngFinish

Jim

Everyday I learn something that somebody else already knew

jaystar
Starting Member

12 Posts

Posted - 2012-06-14 : 09:49:45

Docket_EngFinish is DateTime

and

Docket_Date is DateTime

Thank you

jaystar
Starting Member

12 Posts

Posted - 2012-06-14 : 16:45:40

I want to grab all information between a time on a date to another time on another date everything in between these 4 values

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-14 : 18:10:11

Could you provide some sample data and what results you'd like to see from that data? I'm afraid I'm not fully understanding what exactly you are looking for. The query I provided earlier should have given you what you want, but there was a typo in it.
This
AND (DATEPART(hh , [Docket_EngFinish]) between @sin3 and @sin4
should have been this
AND DATEPART(hh , [Docket_EngFinish]) between @sin3 and @sin4

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources