?
Solved

Find diffference in dates

Posted on 2009-04-29
7
Medium Priority
?
160 Views
Last Modified: 2012-05-06
I think this is correct but wanted to make sure because I am getting no information, but when i put in a static date I get some info.  What I am trying to do is find people that have a dtProcessing  date of 4 days from todays date.  So they have to be within 4 days to show up in the datagrid.
Select	intRecruitID,
	strLastName + ', ' + strFirstName + ', ' + strMiddlename,
	strSSN,
	dtProcessing
from	tblpersonnel
Where	datediff(d, getdate(),  dtProcessing) = 4
Order by strLastName
 
I used this method to test it and found some data but I don't know if its really pulling what I want.
 
Select	intRecruitID,
	strLastName + ', ' + strFirstName + ', ' + strMiddlename,
	strSSN,
	dtProcessing
from	tblpersonnel
Where	datediff(d,'10/7/2002 12:00:00 AM',  dtProcessing) = 4
Order by strLastName

Open in new window

0
Comment
Question by:kdeutsch
  • 3
  • 2
  • 2
7 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24260916
try this (although not optimal):

Select  intRecruitID,
        strLastName + ', ' + strFirstName + ', ' + strMiddlename,
        strSSN,
        dtProcessing
from    tblpersonnel
Where   datediff(d, convert(varchar(10), getdate(), 101),  dtProcessing) = 4
Order by strLastName
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 700 total points
ID: 24261037
I would use this method, as it will take advantage of any indexes that may exist on dtProcessing.


Where      dtProcessing >= dateadd(d, datediff(d, 0, getdate()),4)
  and dtProcessing < dateadd(d, datediff(d, 0, getdate()),5)
0
 

Author Comment

by:kdeutsch
ID: 24261627
BrandonGalderisi,

I understand what the first statement does but what is the second one doing with the 5 at the end.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 60

Expert Comment

by:chapmandew
ID: 24261648
brandon is looking at the date (entire day) between the 4th day and ending at the 5th.
0
 

Author Comment

by:kdeutsch
ID: 24261654
AHHHH, never mind I got it, you are taking any days great than today plus 4 and then any days less than today plus 5
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24261953
Thanks for clarifying in my absence chap.
0
 

Author Closing Comment

by:kdeutsch
ID: 31575964
I need it to calculate the full day, I have another question expaning this to find the days as 4 working days
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

829 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