?
Solved

How to query date ranges with SQL

Posted on 2016-11-16
6
Medium Priority
?
143 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 53

Accepted Solution

by:
Vitor Montalvão earned 2000 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 53

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 53

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

862 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