Solved

How to query date ranges with SQL

Posted on 2016-11-16
6
44 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
  • 3
  • 2
6 Comments
 
LVL 47

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 47

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 47

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

776 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