SQL 2000 Function - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL 2000 Function

Author

Topic

jgzabala@yahoo.com
Starting Member

18 Posts

Posted - 2009-09-14 : 22:02:31

Hi,

Can you help me how to compute difference between
dates. result should be yy/mm/dd. already used
fnTimeApart but i need the total year, month and day between
the records below.

sample
Type Date From date To Year Month Day
LPG 12/09/1995 16/03/1996 0 6 4
LPG 17/03/1996 20/07/1996 0 4 3
LPG 17/08/1996 16/05/1997 0 8 29
LPG 21/10/1997 27/05/1998 0 7 6
LPG 01/05/1999 13/04/2000 0 11 12
LPG 20/08/2002 01/11/2002 0 2 12
LPG 30/04/2003 21/07/2003 0 2 21
LPG 22/07/2003 22/06/2003 0 1 0
LPG 14/10/2004 27/12/2004 0 2 13
LPG 18/03/2005 11/11/2005 0 7 24
LPG 17/03/2006 31/08/2006 0 5 14
LPG 05/09/2006 01/01/2007 0 3 27
LPG 01/01/2007 25/01/2007 0 0 24
LPG 15/06/2007 28/03/2008 0 9 13
LPG 06/11/2008 03/12/2008 0 0 27

thanks
:-)

edit: moved to proper forum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-16 : 13:01:56

[code]SELECT Type,DateFrom,DateTo,CASE WHEN MONTH(DateTo)<MONTH(DateFrom) OR (MONTH(DateTo)=MONTH(DateFrom) AND DATEPART(dd,DateTo)<DATEPART(dd,DateFrom)) THEN DATEDIFF(yy,DateFrom,DateTo)-1 ELSE DATEDIFF(yy,DateFrom,DateTo) END AS Year,
CASE WHEN DATEPART(dd,DateTo)<DATEPART(dd,DateFrom)) THEN DATEDIFF(mm,DateFrom,DateTo)-1 ELSE DATEDIFF(mm,DateFrom,DateTo) END AS Month,
CASE WHEN DATEPART(hh,DateTo)<DATEPART(hh,DateFrom)) THEN DATEDIFF(dd,DateFrom,DateTo)-1 ELSE DATEDIFF(dd,DateFrom,DateTo) END AS Day
FROM YourTable
[/code]

jgzabala@yahoo.com
Starting Member

18 Posts

Posted - 2009-09-17 : 22:07:27

Hi,

thanks for the answer, actually i need
to compute all those dates to generate only
one year/ month / day, summary of all types.

thanks
:-)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-17 : 22:14:40

[code]
select Type, sum(Year), sum(Month), sum(Day)
from
(
< your existing query here>
) a
group by Type
[/code]
this will give you sum() by type. remove Type if you want a overall sum()

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

jgzabala@yahoo.com
Starting Member

18 Posts

Posted - 2009-09-17 : 23:08:43

hi,

thanks for the repy, if i used the
sum it will just sum the year, month and days,
but i need result for example.

Type Year Month Day
LPG 3 12 360

which is based on the dates given
if i used sum the result will be
Type Year Month Day
LPG -1 65 2260

thanks,
:-)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-18 : 00:44:52

how do you want to convert 2260 days to month ? assumed 30 days per month ?

take this line for example :
17/03/1996 20/07/1996 0 4 3

based on above, the different between the 2 date is 0 year 4 month 3 day. The month is it a 30 or 31 day month ?

and just assumed these 2 lines
LPG 17/03/1996 20/07/1996 0 4 3
LPG 17/08/1996 16/05/1997 0 8 29
total up the Year, Month, Day will give you 0 year 12 month 32 days. So how to convert 32 days to month ? can you assume it is a 30 day or 31 day ?

basically summing up the year, month, days does not really make sense or rather it does not give you a meaningful result. Unless you just want an estimate or approximate result than you can assume a 30 days month

just take
2260 % 30 will give you the days
2260 / 30 will give you the month
2260 / (30 * 12) will give you the year

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources