Solved

# GetDate - Data for Night Shift

Posted on 2006-05-12
791 Views
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
Question by:Scotto13

LVL 75

Assisted Solution

WHERE ra.action_time
BETWEEn CONVERT (varchar(10),GETDATE(),101)
AND CONVERT(varchar(10),GETDATE()+1,101)
0

Author Comment

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

LVL 75

Expert Comment

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

Author Comment

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

LVL 68

Accepted Solution

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

Author Comment

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

LVL 68

Expert Comment

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

Author Comment

??

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

Author Comment

I want 12 Noon today, through 12 noon tomorrow.
0

Author Comment

ScottP,

Please clarify,  I need to finish this up.
0

LVL 68

Expert Comment

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

Author Comment

Thanks a lot Scott.
0

## Featured Post

### Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve thâ€¦
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.