Count Number of Records

[StateGov]

I am using SQL 2005 and I would like to count the number of projects (records) between two dates keeping the current date (getdate) in mind. I am trying to find out how many projects exist between these two dates. I have searched the forum and I cannot find anything pertaining to it.

Thanks for your assistance.

 

[gmmastros]

Usually, it's as simple as....

Select Count(*)
From   YourTable
Where  SomeDate >= '2009-09-01'
       And SomeDate < '2009-10-01'

If you want more specific advice, I suggest you post some more details regarding your specific question.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[StateGov]

Thanks for responding so quickly, however, is there a way to automatically calculate the number of projects for a specific timeframe without actually entering dates....like a month at a glance? Thanks.

 

[gmmastros]

It depends on your "month". Do you want a calendar month? the last 4 weeks, the last 30 days, etc...

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[StateGov]

The last month (30 days). Thank you.

 

[gmmastros]

Now we're getting somewhere.

In SQL Server, there is a GetDate() function. This returns the current date and time of the server that SQL Server is running on. You can use GetDate(), but remember that it also returns the time. If you do date calculations, the time will "go with it". For example, you can simply subtract 30 from GetDate(), but the time will be unchanged.

Select GetDate()-30 As ThirtyDaysAgo

You can remove the time component from GetDate() like this...

Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)

So.... the beginning of the day, 30 days ago would look like this:

Select DateAdd(Day, DateDiff(Day, 0, GetDate()-30), 0)

So, your query would be something like....

Select Count(*)
From   YourTable
Where  SomeDate >= DateAdd(Day, DateDiff(Day, 0, GetDate()-30), 0)
       And SomeDate < GetDate()

Anyone working with dates should become familiar with the DateAdd and DateDiff functions. If you are not already familiar with these two functions, I encourage you to spend a couple minutes learning about them.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom