July 13, 2004 at 4:29 am
i have tow long date time filed
How can I show the DATEDIFF between the tow date but!
Not Like this
Date start= datea
Date end = dateb
SELECT newin, - DATEDIFF(d, dateb, datea) AS yom, - DATEDIFF(hh, dateb, datea) AS yomhh
FROM dbo.[local]
Like this
If I have a 28 hour between the tow date fields
I won to show
· 1 day and 4 hour
And if I have only 8 hour
I wont to show
· 0 day and 8 hour
July 13, 2004 at 6:27 am
maybe something along these lines...
declare @d1 datetime
declare @d2 datetime
set @d1 = '2004-05-01 06:40.000'
set @d2 = '2004-05-02 10:00.000'
select cast( datediff(d, @d1, @d2) as varchar(10))
+ ' days and ' +
cast( (datediff(hh, @d1, @d2)%24) as varchar(10))
+ ' hours'
might need a bit of tweaking though - the hours bit will round up, so
set @d1 = '2004-05-02 09:40.000'
set @d2 = '2004-05-02 10:00.000'
above would give "0 days and 1 hours"
jt
July 13, 2004 at 7:37 am
thnks it work 100%
ilan
July 13, 2004 at 10:33 am
nothing like using a sledgehammer to kill a flea, but how this function which you pass the begin date, end date and what date part you want using 1 or 0 (year, month, weeks, days, hours, minutes)
Function is:
Alter FUNCTION ExtendedDateDiff(@BeginDate datetime, @EndDate DateTime,
@iYear integer, @iMonth integer, @iWeek integer, @iDay integer,
@iHour integer, @iMinute integer)
RETURNS varchar(500)
AS
BEGIN
DECLARE @Answer Varchar(500),
@iNum integer
Set @Answer = ''
Set @iNum = 0
if @iYear = 1
Begin
Set @iNum = DateDiff(year, @BeginDate, @EndDate)
Set @BeginDate = DateAdd(year, @iNum, @BeginDate)
set @Answer = @Answer + (Case When @iYear = 1 Then
(Case When @Answer = '' Then '' Else ' And ' End) + Convert(varchar(6), @iNum) + ' years'
Else '' End)
Set @iNum = 0
End
if @iMonth = 1
Begin
Set @iNum = DateDiff(Month, @BeginDate, @EndDate)
Set @BeginDate = DateAdd(Month, @iNum, @BeginDate)
set @Answer = @Answer + (Case When @iMonth = 1 Then
(Case When @Answer = '' Then '' Else ' And ' End) + Convert(varchar(6), @iNum) + ' months'
Else '' End)
Set @iNum = 0
End
if @iWeek = 1
Begin
Set @iNum = DateDiff(wk, @BeginDate, @EndDate)
Set @BeginDate = DateAdd(wk, @iNum, @BeginDate)
set @Answer = @Answer + (Case When @iWeek = 1 Then
(Case When @Answer = '' Then '' Else ' And ' End) + Convert(varchar(6), @iNum) + ' weeks'
Else '' End)
Set @iNum = 0
End
if @iday = 1
Begin
Set @iNum = DateDiff(day, @BeginDate, @EndDate)
Set @BeginDate = DateAdd(day, @iNum, @BeginDate)
set @Answer = @Answer + (Case When @iday = 1 Then
(Case When @Answer = '' Then '' Else ' And ' End) + Convert(varchar(6), @iNum) + ' days'
Else '' End)
Set @iNum = 0
End
if @ihour = 1
Begin
Set @iNum = DateDiff(hh, @BeginDate, @EndDate)
Set @BeginDate = DateAdd(hh, @iNum, @BeginDate)
set @Answer = @Answer + (Case When @iHour = 1 Then
(Case When @Answer = '' Then '' Else ' And ' End) + Convert(varchar(6), @iNum) + ' hours'
Else '' End)
Set @iNum = 0
End
if @iMinute = 1
Begin
Set @iNum = DateDiff(mi, @BeginDate, @EndDate)
set @Answer = @Answer + (Case When @iMinute = 1 Then
(Case When @Answer = '' Then '' Else ' And ' End) + Convert(varchar(6), @iNum) + ' minutes'
Else '' End)
Set @iNum = 0
End
RETURN(@Answer)
END
calling syntax:
Select dbo.ExtendedDateDiff('1/1/2003 1:50 AM', '2/16/2004 7:51 AM', 1, 1, 1, 1, 1, 1)
July 18, 2004 at 1:39 pm
OK it look great but how to create the FUNCTION
and run it after ???
because it not work !!!!
i wont to run the FUNCTION from a View and create a new Colum
thnks a loot
ilan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply