Solved

select only rows in database based on today's date

Posted on 2013-05-27
3
746 Views
Last Modified: 2013-05-27
I am a beginner at SQL. I have MS SQL 2008 database table that has a datetime column. When a new row populates to this table, the server time gets populated to this column.

I need to write three queries:

1. a daily query result of only records with today's date
2. a weekly query with only the last 7 days that runs on Friday (query would be last saturday to this friday)
3. a monthly query run on last day of month.

Any pointers / how to links would be helpful. Thanks in advance.
0
Comment
Question by:Mark B
  • 2
3 Comments
 
LVL 16

Accepted Solution

by:
DcpKing earned 500 total points
ID: 39199851
1. This will give you everthing from the point where the datetime field has the date and all zeroes for the time (i.e. just as today starts).
select *
from <tablename>
where <datefieldname> >= cast(getdate() as date)

Open in new window


2. Same as before, but subtract 6 days (i.e. add negative 6 days).
select *
from tablename
where datefieldname >= dateadd(d -6, cast(getdate() as date))

Open in new window


3. If it's a monthly query you'll run it on the first of the next month, to get everything that happened ... so it'll be up to the start of this month and from the same place last month
select *
from tablename
where datefieldname >= dateadd(m, -1, cast(getdate() as date))
and  datefieldname <= cast(getdate() as date)

Open in new window


hth

Mike
0
 

Author Comment

by:Mark B
ID: 39199895
This worked great, thank you.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39200094
Just here to help :)   In fact, I was doing that just a couple of weeks ago, so the code was fresh in my head.  Hence it was quick to type out and post.  Hope you're having a good weekend.

Mike
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

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. …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

932 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

12 Experts available now in Live!

Get 1:1 Help Now