How to query date ranges with SQL

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.
earwig75Asked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Btw, the above only works for MSSQL 2012 and superior.
0
 
earwig75Author Commented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, you got it ;)
0
 
earwig75Author Commented:
Perfect, thank you.
0
 
_agx_Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.