Solved

How to query date ranges with SQL

Posted on 2016-11-16
6
77 Views
Last Modified: 2016-11-16
Hello, can some help me understand how to query the below date ranges with MS SQL?

The field is called "myDate" . So if Today's date is 11/16/16.....

I need to get records that were created from midnight of the current day, through right now. (11/16/16 12AM - now)

Next, I need to get records created from midnight of the previous day, to records created at midnight of the current day. (11/15/16 12am - 11/15/16 23:59pm)

Next, I need to get records created between 2 days ago and 14 days ago. (11/2/16 12am - 11/13/16 23:59pm)

I know this is confusing, but can someone assist? Thank you.
0
Comment
Question by:earwig75
[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
  • 3
  • 2
6 Comments
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41889699
Would help if you post your query so we can work it out for you.
Anyway, basically this should do for you:
I need to get records that were created from midnight of the current day, through right now
SELECT *
FROM tablename
WHERE myDate BETWEEN CAST(GETDATE() AS DATE) AND GETDATE()

Open in new window


I need to get records created from midnight of the previous day, to records created at midnight of the current day
SELECT *
FROM tablename
WHERE myDate >= CAST(GETDATE()-1 AS DATE) AND myDate < CAST(GETDATE() AS DATE)

Open in new window


I need to get records created between 2 days ago and 14 days ago.
SELECT *
FROM tablename
WHERE myDate >= CAST(GETDATE()-14 AS DATE) AND myDate < CAST(GETDATE()-1 AS DATE)

Open in new window

1
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41889703
Btw, the above only works for MSSQL 2012 and superior.
0
 

Author Comment

by:earwig75
ID: 41889721
Thank you. I have one more question if you don't mind, because I am confused about how this works. If i needed the records for 15-30 Days, would I use:

 WHERE myDate >= CAST(GETDATE()-30 AS DATE) AND myDate < CAST(GETDATE()-14 AS DATE)

?
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41889724
Yes, you got it ;)
0
 

Author Closing Comment

by:earwig75
ID: 41889728
Perfect, thank you.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41889961
Btw, the above only works for MSSQL 2012 and superior.

Good approach. FWIW, should work in 2008+.  IIRC, that is when data type DATE was added.
0

Featured Post

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

687 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