Solved

Sql query with dates in where clause

Posted on 2013-06-04
13
321 Views
Last Modified: 2013-06-11
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
0
Comment
Question by:johnnyg123
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 17

Expert Comment

by:Kent Dyer
ID: 39220194
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
 

Author Comment

by:johnnyg123
ID: 39220264
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39220436
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39220440
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39221256
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39221357
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39222036
Oops, yep: I obviously left off the "+ 1" on the next day check.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39222062
" less than or equal to midnoght"

Ow right, missed that one too, good catch Paul! :)
0
 

Author Comment

by:johnnyg123
ID: 39222405
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39222440
"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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39222444
-- '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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39222452
oooh yes, that's already true - my bad
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39222476
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now