Date and Time - SQL Server Forums

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

Date and Time

Author

Topic

Deon Smit
Starting Member

47 Posts

Posted - 2008-09-03 : 10:42:49

Hi

With the help of a previous post I managed to get the following script.

SELECT distinct batchcartonid, adddate
FROM pickdetail P
WHERE NOT EXISTS
(
SELECT *
FROM cload C
WHERE C.caseid = P.batchcartonid
)
and P.batchcartonid <> ' '
order by adddate

It returns the following

BATCHCARTONID ADDDATE
0000152775 01/09/2008 5:44:19 PM
0000152814 01/09/2008 5:44:20 PM
0000152669 01/09/2008 5:44:21 PM
0000152900 01/09/2008 5:44:22 PM
0000152900 01/09/2008 5:44:28 PM
0000153221 01/09/2008 5:44:44 PM
0000153155 01/09/2008 5:45:11 PM
0000152596 01/09/2008 5:45:24 PM
0000152581 01/09/2008 5:45:33 PM
0000152960 01/09/2008 5:45:39 PM
0000152929 01/09/2008 5:45:48 PM
0000152731 01/09/2008 5:45:56 PM
0000152929 01/09/2008 5:46:02 PM
0000152837 01/09/2008 5:46:10 PM
0000153188 01/09/2008 5:46:24 PM
0000152955 01/09/2008 5:46:27 PM
0000152955 01/09/2008 5:46:49 PM
0000153247 01/09/2008 5:46:55 PM
0000153247 01/09/2008 5:47:08 PM
0000152755 01/09/2008 5:47:12 PM
0000152755 01/09/2008 5:47:21 PM
0000152923 01/09/2008 5:47:25 PM
0000153162 01/09/2008 5:47:36 PM
0000153227 01/09/2008 5:47:41 PM
0000153227 01/09/2008 5:47:43 PM
0000153250 01/09/2008 5:47:44 PM
0000153391 01/09/2008 5:47:45 PM
0000153391 01/09/2008 5:47:52 PM
0000152841 01/09/2008 5:47:54 PM
0000153107 02/09/2008 1:05:07 PM
0000152817 02/09/2008 1:06:20 PM
0000153270 02/09/2008 1:06:38 PM
0000152910 02/09/2008 1:07:15 PM
0000152759 02/09/2008 1:07:33 PM
0000152813 02/09/2008 1:07:37 PM
0000152596 02/09/2008 1:08:08 PM.

I want it to show me all records older than 3 Days.

Regards

Deon Smit
Starting Member

47 Posts

Posted - 2008-09-03 : 10:43:50

That is only a little snip of the data. It goes back 4 months.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-03 : 10:49:55

[code]SELECT DISTINCT pd.batchCartonID,
pd.addDate
FROM pickDetail AS pd
WHERE NOT EXISTS (
SELECT *
FROM cLoad AS c
WHERE c.caseID = pd.batchCartonID
)
AND pd.batchCartonID > ''
AND pd.addDate < DATEADD(DAY, -3, GETDATE())
ORDER BY pd.addDate[/code]

E 12°55'05.63"
N 56°04'39.26"

Deon Smit
Starting Member

47 Posts

Posted - 2008-09-04 : 01:46:19

HTat doesn't work, It complains at line 3. Comamnd not properly ended

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 01:51:35

quote:
Originally posted by Deon Smit

HTat doesn't work, It complains at line 3. Comamnd not properly ended

i dont think error is in solution provided. are you using this as part of some other batch or code. if yes, post your ful code as i suspect error is unincluded code.also i dont think you need distinct as youve having time values also in date and very little chance that two records have same time

SELECT pd.batchCartonID,
		pd.addDate
FROM		pickDetail AS pd
WHERE		NOT EXISTS	(
					SELECT	*
					FROM	cLoad AS c
					WHERE	c.caseID = pd.batchCartonID
				)
		AND pd.batchCartonID > '' 
		AND pd.addDate < DATEADD(DAY, -3, GETDATE())
ORDER BY	pd.addDate

Deon Smit
Starting Member

47 Posts

Posted - 2008-09-04 : 02:33:30

It complains about the DATEADD / invalid identifier
I am running TOAD 8 connecting to Oracle 9i

and P.adddate < DATEADD(DAY, -3, GETDATE())

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 02:52:03

This is not an oracle forum. The solution provided was MSSQL server specific. Please post this oin some oracle related forums if you want oracle specific solution.Anyways try with below

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 02:52:13

This is not an oracle forum. The solution provided was MSSQL server specific. Please post this oin some oracle related forums if you want oracle specific solution

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-04 : 05:07:59

Post your question at www.orafaq.com

Madhivanan

Failing to plan is Planning to fail

Deon Smit
Starting Member

47 Posts

Posted - 2008-09-04 : 05:26:30

Thank You!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources