Difference between dates in the same column

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

Difference between dates in the same column

Author

Topic

cocoon
Starting Member

2 Posts

Posted - 2007-06-23 : 19:49:56

i was working on a appication and just got stuck with this.
I have a column on a table which is a date column and i need to get the days differenct and copy it in another column
e.g

date days between
27-02-2005 1
28-02-2005 0
28-02-2005 0
28-02-2005 1
01-03-2005 0
01-03-2005

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-23 : 19:57:06

[code]DECLARE @TABLE TABLE
(
[date] datetime
)
SET dateformat dmy
INSERT INTO @TABLE
SELECT '27-02-2005' UNION ALL
SELECT '28-02-2005' UNION ALL
SELECT '28-02-2005' UNION ALL
SELECT '28-02-2005' UNION ALL
SELECT '01-03-2005' UNION ALL
SELECT '01-03-2005'

SELECT [date], [days] = ISNULL(DATEDIFF(DAY, [date], (SELECT MIN([date]) FROM @TABLE x WHERE x.[date] > t.[date])), 0)
FROM @TABLE t[/code]

KH
[spoiler]Time is always against us[/spoiler]

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-06-25 : 01:30:31

Hi khtan, u r qry not giving correct result. We need a identity column to get the desired result.

DECLARE @T TABLE
(
[id] int identity(1, 1),
[date] datetime
)

SET dateformat dmy

INSERT INTO @T
SELECT '27-02-2005' UNION ALL
SELECT '28-02-2005' UNION ALL
SELECT '28-02-2005' UNION ALL
SELECT '28-02-2005' UNION ALL
SELECT '01-03-2005' UNION ALL
SELECT '01-03-2005'

SELECT [date], [days] = ISNULL(DATEDIFF(DAY, [date], (SELECT [date] FROM @T x WHERE x.[id] = t.[id] + 1)), 0)
FROM @T t

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-25 : 03:36:18

Check if this also works
SELECT T1.[date], DATEDIFF(DAY, T1.[date], T2.[date]) from @t T1 inner join @t T2 on T2.id=T1.id+1

Madhivanan

Failing to plan is Planning to fail

cocoon
Starting Member

2 Posts

Posted - 2007-07-04 : 12:32:28

thanks PeterNeo madhivanan ur solutions worked

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources