Solved

How to query date ranges with SQL

Posted on 2016-11-16
6
37 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 46

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 46

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 46

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

912 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now