subtract current date from a field in SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

subtract current date from a field in SQL

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-20 : 14:34:29

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-20 : 14:43:27

don't put that in your where clause unless you want to filter on the number of days. You'll want to use the DATEDIFF function on app_rcpdt and getdate().

Jim

Everyday I learn something that somebody else already knew

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-20 : 14:54:49

Okay I tried this and getting:

An expression of non-boolean type specified in a context where a condition is expected, near 'GROUP'

Don't know where to put it if it doesn't go into the Where Clause


SELECT t.Doc, ISNULL(COUNT(cossn),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.cossn IN (SELECT cossn FROM seb2a21.specnew.dbo.people WHERE CompleteDt IS NULL) 
and datediff(dd, getdate(), app_rcpdt)
 GROUP BY Doc

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-20 : 15:37:26

because you put it in your where clause without comparing it to something
and datediff(dd, getdate(), app_rcpdt) what?

and datediff(dd, getdate(), app_rcpdt) = 10 or
and datediff(dd, getdate(), app_rcpdt) < 5, etc.

Jim

Everyday I learn something that somebody else already knew

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-20 : 15:54:42

UNTESTED

SELECT 
	t.Doc, 
	ISNULL(COUNT(clmn),0) AS PendinDDS, 
	MAX(Dib_Mvt_Seq_Num) AS MaxSeq1,
	DateDiff(Day, app_rcpdt,getdate()) As Days_Past
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 DateDiff(Day, app_rcpdt,getdate()) > 7 -- filter rows greater than 7 days.
GROUP BY Doc, app_rcpdt

As used in the Select to display the calculation as a new column.
DateDiff(Day, app_rcpdt,getdate()) As Days_Past
Or
In the where clause as Jim is pointing out. (The WHERE clause is used to filter the data so it needs to be compared against another value, in this case an Integer.)
DateDiff(Day, app_rcpdt,getdate()) > 7 -- filter rows greater than 7 days.

Thank You,

John

X002548
Not Just a Number

15586 Posts

Posted - 2010-12-20 : 16:51:34

and datediff WHAT?

Oh, and the othwer ones posted here will scan

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-20 : 18:35:11

Wow thanks for all of the replies!

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-20 : 19:06:01

where app_rcpdt >= dateadd(day,datediff(day,0,getdate()),0) and
app_rcdt < dateadd(day,datediff(day,-1,getdate()),0)
will give you all the app_recdts that happened today.

Jim

Everyday I learn something that somebody else already knew

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-20 : 19:09:33

Try this and see if you like the result set.

SELECT 
	t.Doc, 
	ISNULL(COUNT(clmn),0) AS PendinDDS, 
	MAX(Dib_Mvt_Seq_Num) AS MaxSeq1,
	DateDiff(Day, app_rcpdt,getdate()) As Days_Past,
	DateAdd(dd,-DateDiff(Day, app_rcpdt,getdate()),GetDate()) As AgeDate
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 DateDiff(Day, app_rcpdt,getdate()) > 7
GROUP BY Doc, app_rcpdt

Thank You,

John

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-20 : 19:53:50

Thanks Jim and John I will take a look at this tomorrow at work and get back to you.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-21 : 11:48:28

Whew asked the client and things were missing from my notes!

He said I need to get the datediff > 300 and < 350

So this works for me:

AND ( Datediff(DAY, flg_cdt, Getdate()) > 300 )
AND ( Datediff(DAY, flg_cdt, Getdate()) < 350 )

Thanks everyone for responding!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-21 : 12:03:26

I'm not sure what your indexing is like, but you should avoid applying functions to your columns. Kinda like Jim's example. Instead you should apply any functions, manipulations or calculations to a variable or, in this case, the GetDate() function.

Something like:

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

Also, you could get rid of the LEFT function and use a LIKE syntax to achieve the same thing and it *might* be faster. But, you are probably still looking at a table scan.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-21 : 13:29:11

Thanks Lamprey but when I tired this I get this error:
Type DATE is not a defined system type.


SELECT 
	t.Doc, 
	ISNULL(COUNT(clmn),0) AS PendinDDS, 
	MAX(Dib_Mvt_Seq_Num) AS MaxSeq1,
	DateDiff(Day, app_rcpdt,getdate()) As Days_Past
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 DateDiff(Day, app_rcpdt,getdate()) > 7 -- filter rows greater than 7 days.
GROUP BY Doc, app_rcpdt

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-21 : 13:31:16

Sorry I meant this to send this query instead:



SELECT 
	t.Doc, 
	ISNULL(COUNT(clmn),0) AS PendinDDS, 
	MAX(Dib_Mvt_Seq_Num) AS MaxSeq1,
	DateDiff(Day, app_rcpdt,getdate()) As Days_Past
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 BETWEEN DATEADD(DAY, -350, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD(DAY, -300, CAST(CURRENT_TIMESTAMP AS DATE))GROUP BY Doc

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-21 : 13:35:48

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

Thank You,

John

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-21 : 13:44:04

Ahh, yeah DATE is for SQL 2008. I was using DATE to strip off the time portion of the DATETIME. I'm not sure if you need to do that or not since the requirement has not been specified percisely enough. If the time doesn't matter then you can remove the CAST. Otherwise you can remove the time by doing: DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-21 : 13:50:04

Thanks that works. Why am I doing it this way instead of the other way?

The field (flg_cdt) is set up as 9/23/2010 12:00:00 AM

So is the bold section changing the datatype to datetime thus dropping the time?

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

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-21 : 13:54:02

If you are wantng to strip the time from a datetime field wouldn't Convert(Varchar,CURRENT_TIMESTAMP,101) achieve it better.

ie.
Select DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)
results in 2010-12-21 00:00:00.000
AND
Select Convert(Varchar,CURRENT_TIMESTAMP,101)
Results in 12/21/2010

Not sure if it makes a difference here though and the conversion to varchar may cause other issues. And I have not used 2008 yet... Does 2008s use of DATE leave it as a DateTime entry?

Thank You,

John

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-12-21 : 14:16:07

Actually I can strip the time from the date from the client side. I'm using 2005.

Don't understand what this means:

CAST(CURRENT_TIMESTAMP AS DATETIME))

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-21 : 15:06:08

quote:
Originally posted by JJ297

<snip>
So is the bold section changing the datatype to datetime thus dropping the time?

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

Nope, the casting to datetime does nothing.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-21 : 15:08:47

quote:
Originally posted by SparkByte

If you are wantng to strip the time from a datetime field wouldn't Convert(Varchar,CURRENT_TIMESTAMP,101) achieve it better.

ie.
Select DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)
results in 2010-12-21 00:00:00.000
AND
Select Convert(Varchar,CURRENT_TIMESTAMP,101)
Results in 12/21/2010

<snip>

It's, generally, bad practice to turn cast datatypes as strings and then back to origianl datatype. Yes, 12/21/2020 is the same, but it's now a string. Sql will do a lot of implicit conversion for you, but try to avoid it if possible. Additionally, date math should be slightly faster. But, propbably by a trivial amount.

Also, if you just do date math you won't run intil any issues with users having different date format settings that could cause unexpected results.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources