Sql query with dates in where clause

I am in the process of creating a new sql job that will execute a stored proc

The job will be scheduled to run on fridays in 5 minute intervals from 8pm to 12am.


part of the stored proc needs to read a table called Player that has the following  fields

playerid, starttime,endtime,coinin,gender

all fields are varchar except starttime and endtime which are datetime



and write records that have a start time that is greater than or equal to 8pm AND an end time that is less than or equal to midnoght (12:00am) to a different table named PlayerActivity



Not sure how to write the query

I know the job will only run on fridays so I know doing a get date will always return a friday date.  The issue I have is the time frame of 8pm to 12am

Given the following sample data


playerid      starttime                                          endtime                                         coinin      gender
7006770      2013-06-07 14:29:27.000      2013-06-07 14:32:40.000      136.25      F
7137160      2013-06-07 14:46:26.000      2013-06-07 14:54:43.000      120.25      M
7137160      2013-06-07 14:54:44.000      2013-06-07 14:54:50.000      1.25      M
7137160      2013-06-07 14:54:59.000      2013-06-07 14:55:56.000      16.25      M
7137160      2013-06-07 20:45:19.000      2013-06-07 20:46:24.000      7.25      M
7050390      2013-06-07 23:37:20.000      2013-06-07 23:59:03.000      1098.00      M


I would want the PlayerActivity table to contain the following (the last 2 records):

 

playerid      starttime                                          endtime                                         coinin      gender
7137160      2013-06-07 20:45:19.000      2013-06-07 20:46:24.000      7.25      M
7050390      2013-06-07 23:37:20.000      2013-06-07 23:59:03.000      1098.00      M
johnnyg123Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Do you want to copy all the rows every 5 minutes?  Or only the rows that originated within the last 5 mins?

If you want them all:


INSERT INTO PlayerActivity ( playerid, starttime, endtime, coinin, gender )
SELECT playerid, starttime, endtime, coinin, gender
FROM dbo.Player
WHERE
    --today at 8PM or later...
    starttime >= DATEADD(HOUR, 20, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AND
    --...but before midnight
    starttime < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
0
 
Kent DyerIT Security Analyst SeniorCommented:
you mean something like this?

select * FROM TABLES (nolock)
WHERE datename(dw,endtime) =6 AND endtime > '08:00 PM' and endtime < '12:00 AM'

Open in new window


Or you could use BETWEEN to achieve the greater than 8 pm and less that 12 am.

HTH,

Kent
0
 
johnnyg123Author Commented:
well

tried the query and no rows were returned


Also, need the startdate to be greater than 8pm and the end date is than 12am (for each record)
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Scott PletcherSenior DBACommented:
You should always avoid using functions on table columns if at all possible: instead, manipulate system dates and/or variables as required to get the comparisons you need.
0
 
ValentinoVBI ConsultantCommented:
Great advice by Scott, as usual! (wish EE let us vote up :))

Little remark though: the "before midnight" test is one day off and as per specs should be based on endtime:

endtime < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)

Can also be written as:

DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)
0
 
PortletPaulCommented:
NO points pl.
>>less than or equal to midnight (12:00am)  
midnight is treated as 00:00:00 +0000 i.e. the start of the next day

so if you really do want midnight included, change the less than
[starttime] >= [8pm] and [endtime] < [midnight]

to less than or equal
[starttime] >= [8pm] and [endtime] <= [00-the-next-day]

nb follow the advice added by ValentinoV to add a day regardless of including or excluding midnight. hope this isn't too cryptic.
0
 
Scott PletcherSenior DBACommented:
Oops, yep: I obviously left off the "+ 1" on the next day check.
0
 
ValentinoVBI ConsultantCommented:
" less than or equal to midnoght"

Ow right, missed that one too, good catch Paul! :)
0
 
johnnyg123Author Commented:
I really do appreciate all the responses!

I think one thing I didn't explain as well as I should have is that
the table I am trying to extract data from will contain records from dates other than the
friday date that I am interested in


So....

Here is what I have so far

INSERT INTO PlayerActivity ( playerid, starttime, endtime, coinin, gender )

SELECT playerid, starttime, endtime, coinin, gender
FROM dbo.Player
WHERE
    --today at 8PM or later...
    starttime >= DATEADD(HOUR, 20, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AND
    --...but before midnight
    endtime < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)

I need to add an additional criteria to make sure the date portion is equal to the friday

I am using sql 2008 so I think I can use some version of

      select CONVERT(date, getdate()) but can't seem to get that to work
0
 
ValentinoVBI ConsultantCommented:
"I need to add an additional criteria to make sure the date portion is equal to the friday"

Isn't that automatically taken care of by the startdate/enddate filter?  If both StartDate and EndDate are for a Friday, you will only see results for that particular Friday.
0
 
PortletPaulCommented:
-- 'truncates' getdate() to start of the day
-- assumes (as you state) you are running only on friday
and starttime >= dateadd(day, datediff(day,0, getdate() ), 0)
0
 
PortletPaulCommented:
oooh yes, that's already true - my bad
0
 
PortletPaulCommented:
for a test of Friday, that is independent of dbms settings that affect first day of week:

and (datediff(day,0, getdate() ) % 7) = 4 /* using % 7: 0 is Monday, 6 is Sunday */

but I'm not sure you need it
0
All Courses

From novice to tech pro — start learning today.