subtracting current date from app_rcpdt to determine age

[nc297]

What am I missing error codes says:
The dateadd function requires 3 argument(s).

I need to subtract the current date from app_rcpdt, the bold section below.



SELECT t.Doc, ISNULL(COUNT(clmn),0) AS PendinDDS, MAX(Dib_Mvt_Seq_Num) AS MaxSeq1
FROM T2DibPend t
WHERE Dib_Mvt_Typ = 'R' and
LEFT(Org_Id,1) IN ('R','S','V') AND
t.clmn IN (SELECT clmn FROM specnew.dbo.people
WHERE CompleteDt IS NULL) and app_rcpdt - dateadd(dd, getdate())
GROUP BY Doc, app_rcpdt

 

[bborissov]

You should use DateDiff() function
and this code:
app_rcpdt - dateadd(dd, getdate())

should be:

datediff(dd, app_rcpdt, getdate()) > ???????

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[sparkbyte]

This seems to be posted on both SQL forums

SQLteam.com - subtract current date from a field in SQL

Thanks

John Fuhrman

 

[nc297]

Yes it is posted on both I didn't know you guys look at both forums.

Just in case... I need to subtract the current date from app_rcpdt (which is a date field) to determine the age of the clmn.

So would this work?


and datediff(dd, getdate(), app_rcpdt) = NOW

 

[markros]

What is now - the number of days?

To find number of days between two dates you need to use DATEDIFF function.

If you want to check, if particular item is less than 30 days old, use

WHERE DateFld < dateadd(days, -30, getdate())

This will check if the date field is less than 30 days old starting from the exact moment. If you're using SQL Server 2008, you may want to cast getdate() to date first to remove time portion from the datetime value.


PluralSight Learning Library

 

[nc297]

Just found my notes...I'm comparing two fields. I'm adding
these two fields:

PendInFO + PendINDDS then I want to get the age of the clmn by subtracting get date from the field. Does this make sense?

Select t.doc sum(pendinfo + pendindds) as TotalPend
from t2counts
where datediff(dd, app_rcpdt, getdate()) > getdate

Don't know what to compare it to to get the clmn age. Will have to ask client tomorrow.

 

[markros]

I think you need to post your table structure as CREATE TABLE statement, explain what do you want to achieve (preferably with insert statement to show some input data) and desired result. Then we will be able to help.

PluralSight Learning Library

 

[sparkbyte]

No, it doesn't. There isn't enaough info here as Mark is suggesting. Mark's request would be best.

but, when it pendinfo and pendindds?? Are these dtaetime fields or integers??
For sum(pendinfo + pendindds) as TotalPend to work they would need to be numbers. ie. ( 2 + 2 ) = TotalPend and not ('11/16/2010' + '12/20/2010') = TotalPend.
If they are dates they need to be "converted" to numbers.

As a variable...

/***********************************************************************
  Set variables with arbitary values for example
************************************************************************/

Declare @pendinfo  DateTime 
Set @pendinfo  = '12/4/2010 12:47 PM'

Declare @pendindds DateTime
Set @pendindds = '11/14/2010 09:36 AM'

/***********************************************************************
  Set variable with the amount of days between @pendinfo and @pendindds
  making sure the number is positive for use in further calculations.
************************************************************************/
Declare @t1 int 
Set @t1=0
	If datediff(Day, @pendinfo, @pendindds) <= 0 
		Begin
			Set @t1 = datediff(Day, @pendindds, @pendinfo)
         -- print 'Less than Zero'

		End
	Else
		Begin
			Set @t1 = datediff(Day, @pendinfo, @pendindds)
         -- print 'Greater than Zero'
		End

print @t1 -- Print the contents of the variable (should be a positvive number)

20

or as a calculated column

/***********************************************************************
  Set variables with arbitary values for example
************************************************************************/

Declare @pendinfo  DateTime 
Set @pendinfo  = '12/4/2010 12:47 PM'

Declare @pendindds DateTime
Set @pendindds = '11/14/2010 09:36 AM'

/***********************************************************************
  Use Case to set the value of TotalPend to days between
  @pendinfo and @pendindds making sure the number is positive
  for use in further calculations.
************************************************************************/

Select 
	Case when datediff(Day, @pendinfo, @pendindds) <= 0
		Then datediff(Day, @pendindds, @pendinfo)
		Else datediff(Day, @pendinfo, @pendindds)
	End As TotalPend

TotalPend
-----------
20

With that said, maybe this would be close.

Select 
	t.doc,
	Case when datediff(Day, pendinfo, pendindds) <= 0
		Then datediff(Day, pendindds, pendinfo)
		Else datediff(Day, pendinfo, pendindds)
	End As TotalPend 
from t2counts
where datediff(dd, app_rcpdt, getdate()) > getdate

Hope this helps

Thanks

John Fuhrman

 

[nc297]

Thanks Guys but I spoke to the client and he left somethings out. I need to get the count between > 300 and < 351.

Pendinfo + penddds are both int fields so I'm able to add them together. Thanks Sparkbyte you helped me learn a lot as well and understand datediff and when to use it.

 

[sparkbyte]

Have you tried Lamrey's suggestion yet from SQLteam?

WHERE flg_cdt BETWEEN DATEADD(DAY, -350, CURRENT_TIMESTAMP) AND DATEADD(DAY, -300, CURRENT_TIMESTAMP)


Thanks

John Fuhrman

 

[nc297]

Yes I have and it works! Thanks. I have another question but will open up a new post.

 

[gmmastros]

nc297,

You have asked several questions recently. In each case, the actual question and solution have been relatively simple. This is not too surprising since you seem like a relatively new SQL Server developer (nothing to be ashamed of).

I encourage you to red this:

http://support.microsoft.com/kb/555375

If you do, and if you follow the advice there, I am certain that you will have much better luck getting timely answers to your questions. The simple reality is: the better written questions get answered quicker, because people that tend to answer questions don't need to guess about your intent. I'm not talking about dotting your i's or crossing your t's. My intent is to show you that including all the relevant information is important.

Asking a good question will improve the likelihood of a timely and acceptable solution.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nc297]

Thank you gmmastros I will take a look at the link you've sent. I am fairly new to SQL I guess you can tell. I will work on writing better questions so I can get a quick response. Thanks for all of your patience and teaching me so many things.

I just sent another questions I hope it was well formed.

 

[nc297]

Thanks again for the link I've printed it out and will make certain I have all neccessary information in my question before posting.