DateDiff in sql? - 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

DateDiff in sql?

Author

Topic

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-10-19 : 02:53:28

Hi,
I have a Table Where I have Two Date columns and I want to find the Differnce in the 3rd column.ie DateReceived - AsOnDate = NoofDays.I tried this code


SELECT  SRNO,CONVERT (VARCHAR,AsonDate,102)as ASONDATE,CONVERT(VARCHAR, DateReceived, 102) AS DateReceived,CONVERT(VARCHAR, DateAcknowledged, 102) AS NoofDays,
sum(CASE WHEN DATEDIFF(DAY, DATERECEIVED,ASONDATE, GETDATE()) 
				FROM Details

Plz help me...

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-19 : 03:02:37

Where do I even begin? Please explain the logic behind the code, what you are trying to accomplish.

SELECT		SRNO,
		CONVERT(VARCHAR, AsonDate,102) as ASONDATE,
		CONVERT(VARCHAR, DateReceived, 102) AS DateReceived,
		CONVERT(VARCHAR, DateAcknowledged, 102) AS NoofDays,
		sum(CASE WHEN DATEDIFF(DAY, DATERECEIVED, ASONDATE, GETDATE()) 
FROM		Details

1) A sum is an aggregation. Therefore you [in this case] need a GROUP BY. That is missing
2) DATEDIFF functin only takes three parameters
3) Last row before FROM is missing a paranthesis
4) A case statement needs at least one WHEN keyword
5) A case statement needs a END at the ending

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-19 : 03:05:10

Maybe this will be clearer to you?

SELECT		SRNO,
		CONVERT(VARCHAR, AsonDate,102) as ASONDATE,
		CONVERT(VARCHAR, DateReceived, 102) AS DateReceived,
		CONVERT(VARCHAR, DateAcknowledged, 102) AS NoofDays,
		ABS(DATEDIFF(DAY, DATERECEIVED, ASONDATE))
FROM		Details

Peter Larsson
Helsingborg, Sweden

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-10-19 : 03:15:04

ok,
Here is my Entire Code.



SELECT  SRNO,REFNO,REFERENCE,NAME,PHONE,PLACE,BRANCH,MODEL,HANDLEDBY,LU,CONVERT (VARCHAR,AsonDate,102)as ASONDATE,CONVERT(VARCHAR, DateReceived, 102) AS DateReceived,CONVERT(VARCHAR, DateAcknowledged, 102) AS DateAcknowledged,ComplaintOpen,remarks,status,
				sum(CASE WHEN DATEDIFF(DAY, DATERECEIVED, GETDATE()) > 7 AND LU = 'L' THEN 1 ELSE 0 END) 'Within 7 days',
				sum(CASE WHEN DATEDIFF(DAY, DATERECEIVED, GETDATE()) > 15 AND LU = 'U' THEN 1 ELSE 0 END) 'Within 15 days'
				FROM amcmcallDetails
				WHERE Status = 'P' 
		                GROUP By SRNO,REFNO,REFERENCE, name,datelos,phone,place,branch,model,handledby,LU,asondate,datereceived,dateacknowledged,complaintopen,remarks,status
				HAVING SUM(CASE WHEN DATEDIFF(DAY, DATERECEIVED, GETDATE()) > 7 AND LU = 'L' THEN 1 ELSE 0 END) > 0
				OR SUM(CASE WHEN DATEDIFF(DAY, DATERECEIVED, GETDATE()) > 15 AND LU = 'U' THEN 1 ELSE 0 END) > 0
				order by convert(int,srno)

I want to Calculate the difference between datereceived and asondate and store the difference in new column Days.Hope I have made it clear now.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-19 : 03:39:46

Everything seems ok, except that DATELOS column in the GROUP BY statement.
Comment out DATELOS column in the GROPU BY and try again.

Peter Larsson
Helsingborg, Sweden

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-10-19 : 05:11:34

Thanks...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-19 : 05:14:13

Does the query work now?

Peter Larsson
Helsingborg, Sweden

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-10-19 : 08:32:18

Yeah,
Its working perfectly....Thanks