Solved

Sql query with dates in where clause

Posted on 2013-06-04
13
323 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:
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

816 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

13 Experts available now in Live!

Get 1:1 Help Now