Simone1


I am trying to write a stored procedure that will select information from a SQL table based on a specific time.
For example I have a name field and a time field, I want to return just the names that were created between a specific time frame. ex between 3pm and 4pm.
Any thoughts



Re: Selecting data from SQL table based on a time period

Kent Waldrop Oc07


You need to do something like

Code Block

select name

from yourTable

where time

between '3PM' and '4PM'

with a number of caveats! For one thing, this will work ONLY if your time field does in fact contain time only. If your "time" field also contains a "date" component then you will need to strip out the data component. If your "Time" field also includes a date component you can do something like:

Code Block

select name
from yourTable
where time - floor(convert(float,time)) between '3PM' and '4PM'

for instance:

Code Block

select getdate()
where getdate() - floor(convert(float,getdate())) between '3PM' and '4PM'

/*
-----------------------
2007-10-11 15:53:50.857
*/






Re: Selecting data from SQL table based on a time period

Frank Kalis

You could use CONVERT along with style 108 in your WHERE clause.







Re: Selecting data from SQL table based on a time period

Simone1

I am not sure what style 108 is.




Re: Selecting data from SQL table based on a time period

Frank Kalis

You can read about CONVERT() along with its various styles in the SQL Server Books Online. I posted it, because I assume that your "time" column is of a datetime data type.






Re: Selecting data from SQL table based on a time period

Simone1

Acutally it is and I was wonder how I could just pull out the time.




Re: Selecting data from SQL table based on a time period

Kent Waldrop Oc07

One way is something like:

Code Block

declare @test table
( name varchar(20),
time datetime
)
insert into @test
select 'Barney Rubble', '2007-10-05 10:05' union all
select 'Fred Flintstone', '2007-10-05 15:45' union all
select 'Betty Rubble', '2007-10-08 15:08'
--select * from @test

select name, convert(varchar(8), time, 108) as time from @test
where time - floor(convert(float, time)) between '3PM' and '4PM'

/*
name time
-------------------- --------
Fred Flintstone 15:45:00
Betty Rubble 15:08:00
*/


select name, convert(varchar(8), time, 108) as time from @test
where time - floor(convert(float, time)) between '15:00' and '15:30'

/*
name time
-------------------- --------
Betty Rubble 15:08:00
*/

(I edited this to contain Frank's CONVERT with 108 -- his idea is a good idea.)





Re: Selecting data from SQL table based on a time period

Frank Kalis

SELECT *

FROM your_table

WHERE CONVERT(CHAR(8), time, 108)

BETWEEN '15:00:00' AND '16:00:00'






Re: Selecting data from SQL table based on a time period

Bushan

If you have any indexes defined on Time column, you can use the following SQL ...

Code Block

select * from YourTable
where Time
between
cast('01/01/1900 14:00:000' as datetime) -- Change this lowest possible date
and
cast(convert(varchar(10), Getdate(), 101) +' 17:00:000' as datetime)





Re: Selecting data from SQL table based on a time period

Arnie Rowland

Bushan,

I think that 'might' include 17:01 PM on the 'lowest possible date', as well as 13:59:59 on getdate() -and a lot of other unwanted stuff inbetween ...






Re: Selecting data from SQL table based on a time period

Frank Kalis

Ahem, this is going to return all rows between 19000101 and now and therefore is likely to produce wrong results. Only the time portion is relevant here. The date portion needs to be ignored.






Re: Selecting data from SQL table based on a time period

Frank Kalis

Oops, my reply was aimed at Bushan's, not yours Arnie.






Re: Selecting data from SQL table based on a time period

Simone1

Acutally i wont be doing it by time anymore.
Here is what I have:
I have a table with employee IDs, this table can have the same employee ID many times. Based on the Employee ID I would like the SUM of all the numbers in the Numbers column.
I am not sure how to do math in a stored procedure.




Re: Selecting data from SQL table based on a time period

Arnie Rowland

Use a GROUP BY, with a sum( Numbers ).

Something like:

Code Block

SELECT

EmployeeID,

EmployeeTotal = sum( Numbers )

FROM MyTable

GROUP BY EmployeeID






Re: Selecting data from SQL table based on a time period

Simone1

What is EmployeeTotal Do I need to declare it
This is what i was thinking:

SELECT Max(EmployeeID)

FROM Employeetable
WHERE (I would do the sum somewhere here i think)

Based on the highest EmployeeID number I want to get the sum of the numbers column for that Employee