?
Solved

SQL Query : GetDate() - 60 question

Posted on 2012-08-20
21
Medium Priority
?
1,246 Views
Last Modified: 2012-08-22
Good Day Experts!

I have a question about my query t be sure I am structuring the query how I am "thinking".

I want to get a count of records from a table from todays date back 60 days.  So, I have this query:

SELECT     count(*)
FROM         [WEB PAIDFILE]
WHERE     ([Carrier Number] = '20032') AND ([Bill Date] <= (GetDate() - 60))

Will this give me the count of the subset of data from today through 60 days prior to today?

Thanks,
jimbo99999
0
Comment
Question by:Jimbo99999
  • 9
  • 4
  • 3
  • +5
21 Comments
 
LVL 13

Assisted Solution

by:LIONKING
LIONKING earned 100 total points
ID: 38313152
Give this a try:

SELECT     count(*)
FROM         [WEB PAIDFILE]
WHERE     ([Carrier Number] = '20032') AND ([Bill Date] >= CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,DATEADD(d,-60,GETDATE()))),102) )

Open in new window


The difference is that my script counts from the 00:00:00 hours, your script is taking into account the current time.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38313165
Yes, but you'll want to be careful with the time component of GETDATE() to make sure it returns the values you wish.

Example:  Getdate() returns '2012-08-20 13:47:15.010', DATEADD(d, -60, GETDATE()) and GETDATE() - 60 both return '2012-06-21 13:47:15.010'

Question:  Do you want to return rows that have a time of '2012-06-21 00:00:00.00' ?
If yes, you'll need to CAST({your date calculation} as date) to just use the day part.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38313178
SELECT     count(*)
FROM         [WEB PAIDFILE]
WHERE     ([Carrier Number] = '20032')
AND BILLDATE >= DATEADD( dd,-60, convert(varchar,getdate(),112) )
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 13

Expert Comment

by:dwkor
ID: 38313179
It would work (both methods - yours and LionKing). Internally datetime is stored as float where date part is the int and time part is the number after floating point. So it can be casted back and force to float.

Same time, I would advice you against that method. New SQL Server 2008 datetime types (date2, datetime2, etc) are not compatible with such implementation. It's safer to use dateadd method which will work with either system type. While it does not matter for GetDate() call - it's good habit to acquire:

SELECT     count(*)
FROM         [WEB PAIDFILE]
WHERE     ([Carrier Number] = '20032') AND ([Bill Date] <= dateadd(day,-60,GetDate()))

Open in new window


If you need date part only - you can do something like that:
SELECT     count(*)
FROM         [WEB PAIDFILE]
WHERE     ([Carrier Number] = '20032') AND ([Bill Date] <= dateadd(day,datediff(day,'2001-01-01',GetDate()) - 60, '2001-01-01'))

Open in new window

0
 

Author Comment

by:Jimbo99999
ID: 38313772
Hello:

I looked in the data and all of the records have not time on the back of the date...just 00:00:00.000.

Thanks,
jimbo99999
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38314680
Internally datetime is stored as float where date part is the int and time part is the number after floating point.
Actually no.  In SQL Server datetime has always been stored as two 4 byte integers: One for the date and one for the time.  In much the same way, smalldatetime is also stored as two 2 byte integers.
0
 
LVL 9

Assisted Solution

by:keyu
keyu earned 100 total points
ID: 38315056
SELECT     count(*)
FROM         [WEB PAIDFILE]
WHERE     ([Carrier Number] = '20032') AND ([Bill Date] <=  dateadd(day,-60,GetDate() ) )


 you can also use datediff function

declare @newdate as datetime
set @newdate=dateadd(day,-60,GetDate() )

SELECT     count(*)
FROM         [WEB PAIDFILE]
WHERE     ([Carrier Number] = '20032') AND datediff(day,[Bill Date],[@newdate] )>=0
0
 

Author Comment

by:Jimbo99999
ID: 38315903
Thanks for all of the suggestions...I am back on the project this morning.  The query takes a while to run as the table has over a million records. I will post back when I get some results.
0
 

Author Comment

by:Jimbo99999
ID: 38316083
Ok, 27mins later I have results that I don't understand.  Here is the query:

SELECT     count(*), [Bill Date]
FROM         [WEB PAIDFILE]
WHERE     ([Carrier Number] = '20032') AND ([Bill Date] <=  dateadd(day,-60,GetDate() ) )
group by [Bill Date]

I was anticipating the count of records for the [Bill Date] from todays date back to back 60 days.  I have attached a screen print of the results.
output.doc
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38316120
Correct, if you group by Bill Date, then you're going to get counts for each distinct billdate value.

First off, copy-paste the output into a spreadsheet, and sum up the first column, so you know the overall count.

Then run this and see if it returns just that count:

SELECT     count([Bill Date]) as the_count
FROM         [WEB PAIDFILE]
WHERE     ([Carrier Number] = '20032') AND ([Bill Date] <=  dateadd(day,-60,GetDate() ) )
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38316126
>from todays date back 60 days.
Also, looking at the dates in the output I think you need to change the WHERE clause to greater than, so that the range is (60 days ago to today), as it appears to be returning (from the beginning of time to 60 days ago)

SELECT     count([Bill Date]) as the_count
FROM         [WEB PAIDFILE]
WHERE     ([Carrier Number] = '20032') AND ([Bill Date] >=  dateadd(day,-60,GetDate() ) )
0
 

Author Comment

by:Jimbo99999
ID: 38316188
Thanks jimhorn...I will try that now to see if I get the correct dates.  With all the records in the table, it will probably take about 1/2hr.
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 400 total points
ID: 38316215
<Just to check the box>

If there will ever be a billdate greater than GetDate(), and you would not want that in your query results, then you'd have to add that as well, like this..

SELECT     count([Bill Date]) as the_count
FROM         [WEB PAIDFILE]
WHERE     [Carrier Number] = '20032'
   AND ([Bill Date] BETWEEN dateadd(day,-60,GetDate() ) AND GETDATE() )

Also, a couple of others here posted a solution with DATEDIFF as opposed to DATEADD.
Since you're banging against a million rows, it's probably worth running it both ways to see which one executes faster.
0
 

Author Comment

by:Jimbo99999
ID: 38316411
Ok, I will try DATEDIFF.  What is the differing processing ideaologies between the 2?

After inquiry, there will not be [Bill Date] greater than GetDate().  

Here is my query that took 16mins(output is as desired with count by each day for current back 60days):

SELECT     count(*), [Bill Date]
FROM         [WEB PAIDFILE]
WHERE     ([Carrier Number] = '20032') AND ([Bill Date] >=  dateadd(day,-60,GetDate() ) )
Group by [Bill Date]
Order by [Bill Date] desc
0
 

Author Comment

by:Jimbo99999
ID: 38316562
I tried DATEDIFF instead of DATEADD and it returned no records.
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 38316762
NEVER use a function on a column if it can be avoided.

The "standard" way now to strip the time from a date is:
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)


SELECT     count(*), [Bill Date]
FROM         [WEB PAIDFILE]
WHERE     ([Carrier Number] = '20032') AND ([Bill Date] >=  dateadd(day,-60,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) ) )
Group by [Bill Date]
Order by [Bill Date] desc


16+mins for only ~1M rows??  That's seem extraordinarily slow.  Is it a view or a table that you're reading?
0
 

Author Comment

by:Jimbo99999
ID: 38318204
It is a table.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38318322
That table almost certainly needs a rebuild.  You can determine that for sure by running this query:


SELECT *
FROM sys.dm_db_index_physical_stats ( DB_ID(), OBJECT_ID('WEB PAIDFILE'), NULL, NULL, 'LIMITED')
0
 

Author Comment

by:Jimbo99999
ID: 38320290
I inadvertantly mis"spoke" on the size of the table.  There are 18,363,077 records. In light , perhaps the speed is acceptable.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38320818
Still, check the table to see if it needs rebuilt.
0
 

Author Closing Comment

by:Jimbo99999
ID: 38320977
Thanks everyone for your repsonses...I learned quite a bit with all the exchanges.

jimbo99999
0

Featured Post

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.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Screencast - Getting to Know the Pipeline

807 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