Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

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
0
johnnyg123
Asked:
johnnyg123
  • 4
  • 3
  • 3
  • +2
1 Solution
 
Kent DyerCommented:
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
 
Scott PletcherSenior 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now