• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 807
  • Last Modified:

GetDate - Data for Night Shift

I currently have a query that uses this condition:

where ra.action_time between DATEADD(day,-1,GETDATE()) and getdate()

I want this to be changed to pull 12:00 PM today through 12:00 PM tomorrow.  Can someone please make recomendations?

Scotto13
0
Scotto13
Asked:
Scotto13
  • 7
  • 3
  • 2
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
WHERE ra.action_time
BETWEEn CONVERT (varchar(10),GETDATE(),101)
AND CONVERT(varchar(10),GETDATE()+1,101)
0
 
Scotto13Author Commented:
That works.  I am having a change of mind.  Please follow me here and offer any advice.

If I am working through the night and I run the query at 10 PM...fine.  But I think at 1 AM, it will not produce the desired results.  At 1 AM, it will pull data from that point forward.  This will leave the 12PM to 12AM data out.  Right?  Please offer ideas.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Scotto13,
> This will leave the 12PM to 12AM data out.  Right?

If you run this on May13th at 1 am, you Query will be from 13th may 12.00 am and 14th may 12.00 am

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Scotto13Author Commented:
ok, that's not what I need.  Here are two scenarios

RunDate        Returned data
5/12 10PM    5/12 12PM - 5/13 - 12PM
5/13 1AM     5/12 12PM - 5/13 - 12PM

5/13 8PM     5/13 12PM - 5/14 - 12PM
5/13 7AM     5/13 12PM - 5/14 - 12PM

Possible?
0
 
Scott PletcherSenior DBACommented:
Do you want 12 PM (noon) or 12AM (midnight)?

You just have to decide a cutoff time.  Say we use 6AM (any request on/before 6AM will get the prior day's data):

WHERE ra.action_time BETWEEN DATEADD(DAY, CASE WHEN DATEPART(HOUR, GETDATE()) <= 6 THEN -1 ELSE 0 END, GETDATE()) AND DATEADD(DAY, CASE WHEN DATEPART(HOUR, GETDATE()) <= 6 THEN 0 ELSE +1 END, GETDATE())
0
 
Scotto13Author Commented:
that's cool!  I love SQL.  Now I need to learn it.  Thanks Scott.

Split for aneeshattingal getting me headed where I needed to go.
0
 
Scott PletcherSenior DBACommented:
D'OH, CORRECTION, I FORGOT TO REMOVE TIME:

The last GETDATE() in each section should be replaced with CONVERT(CHAR(8), GETDATE(), 112) (assuming you want midnight :-) ); specifically:

WHERE ra.action_time BETWEEN DATEADD(DAY, CASE WHEN DATEPART(HOUR, GETDATE()) <= 6 THEN -1 ELSE 0 END, CONVERT(CHAR(8), GETDATE(), 112) AND DATEADD(DAY, CASE WHEN DATEPART(HOUR, GETDATE()) <= 6 THEN 0 ELSE +1 END, CONVERT(CHAR(8), GETDATE(), 112))
0
 
Scotto13Author Commented:
??  

Now I'm confused.  What is the cose above missing that is needed (in the code below)

When I run the last code, I get an error.
0
 
Scotto13Author Commented:
I want 12 Noon today, through 12 noon tomorrow.
0
 
Scotto13Author Commented:
ScottP,

Please clarify,  I need to finish this up.
0
 
Scott PletcherSenior DBACommented:
Ooh, sorry, if you do want noon, then:


WHERE ra.action_time BETWEEN DATEADD(DAY, CASE WHEN DATEPART(HOUR, GETDATE()) <= 6 THEN -1 ELSE 0 END, DATEADD(HOUR, 12, CONVERT(CHAR(8), GETDATE(), 112)))  AND DATEADD(DAY, CASE WHEN DATEPART(HOUR, GETDATE()) <= 6 THEN 0 ELSE +1 END, DATEADD(HOUR, 12, CONVERT(CHAR(8), GETDATE(), 112)))
0
 
Scotto13Author Commented:
Thanks a lot Scott.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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