Solved

Sql query with dates in where clause

Posted on 2013-06-04
13
326 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:
Scott Pletcher 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 69

Expert Comment

by:Scott Pletcher
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
 
LVL 69

Expert Comment

by:Scott Pletcher
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

751 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