date 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

SQL Server Development (2000)

date function

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-03-17 : 10:35:12

Please check this function to see if it is correct.
It is supposed to receive hours and return the number of days + hours + minutes

ALTER FUNCTION dbo.fn_ConvertMinutes(@Minutes INT) RETURNS VARCHAR(100) AS
BEGIN
DECLARE @Days INT
DECLARE @Hours INT
DECLARE @MinutesLeft INT
DECLARE @Remainder INT

SET @Days = FLOOR(@Minutes / (24 * 60))
SET @Remainder = @Minutes - (@Days * 24 * 60)
SET @Hours = FLOOR(@Remainder / 60)
SET @MinutesLeft = @Remainder - (@Hours * 60)

RETURN CONVERT(VARCHAR(100), @Days) + ' Days ' + CONVERT(VARCHAR(100), @Hours) + ' Hours ' + CONVERT(VARCHAR(100), @MinutesLeft) + ' Minutes'

END

Thanks

SqlStar
Posting Yak Master

121 Posts

Posted - 2005-03-17 : 23:56:02

Yes.This function is correct.

You may change like this "SET @Remainder = @Minutes % (24 * 60)" Instead of
"SET @Remainder = @Minutes - (@Days * 24 * 60)"

:) While we stop to think, we often miss our opportunity :)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-18 : 08:59:41

This would also do the job for you:

alter function dbo.fn_ConvertMinutes(@Minutes int)
returns	varchar(100)
as
begin
return
convert(varchar(100),floor(@Minutes / (24 * 60))) +' Days ' +
convert(varchar(100),datepart(hh,dateadd(mi,@Minutes,0))) + ' Hours ' +
convert(varchar(100),datepart(mi,dateadd(mi,@Minutes,0))) + ' Minutes'
end

CODO ERGO SUM

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-18 : 10:26:21

quote:

Originally posted by Michael Valentine Jones

This would also do the job for you:

alter function dbo.fn_ConvertMinutes(@Minutes int)
returns	varchar(100)
as
begin
return
convert(varchar(100),floor(@Minutes / (24 * 60))) +' Days ' +
convert(varchar(100),datepart(hh,dateadd(mi,@Minutes,0))) + ' Hours ' +
convert(varchar(100),datepart(mi,dateadd(mi,@Minutes,0))) + ' Minutes'
end

CODO ERGO SUM

Colonel Jones,

I don't think this works. I cut and pasted and I get syntax errors and a statement that you can't use the RETURN statement in this fashion. Check it out for yourself. I see your idea, I'm just unsure why you wanted to use the RETURN statement like this.

Semper fi, Gunnery Sergeant Fowler...

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-18 : 14:32:12

The error you saw is probably because I left it with an alter function, instead of create function. I tested the code below, and it seems to work OK. Or at least it produces the output shown.

I was just trying to show an alternative using SQL Date functions to get the hours and minutes. I also hard coded 1440 (minutes/day) and removed the floor function this time; no reason to calculate minutes/day every time and integer division drops the remainder anyway.

drop function dbo.fn_ConvertMinutes
go
create function dbo.fn_ConvertMinutes(@Minutes int)
returns	  varchar(100)
as
begin
return
convert(varchar(100),@Minutes/1440) +' Days ' +
convert(varchar(100),datepart(hh,dateadd(mi,@Minutes,0))) + ' Hours ' +
convert(varchar(100),datepart(mi,dateadd(mi,@Minutes,0))) + ' Minutes'
end
go

select Days_Mours_Minutes = dbo.fn_ConvertMinutes(2870)

Output:
Days_Mours_Minutes
---------------------------
1 Days 23 Hours 50 Minutes

quote:
Originally posted by Xerxes

quote:
Originally posted by Michael Valentine Jones

This would also do the job for you:
alter function dbo.fn_ConvertMinutes(@Minutes int)
returns varchar(100)
as
begin
return
convert(varchar(100),floor(@Minutes / (24 * 60))) +' Days ' +
convert(varchar(100),datepart(hh,dateadd(mi,@Minutes,0))) + ' Hours ' +
convert(varchar(100),datepart(mi,dateadd(mi,@Minutes,0))) + ' Minutes'
end

CODO ERGO SUM

Colonel Jones,

I don't think this works. I cut and pasted and I get syntax errors and a statement that you can't use the RETURN statement in this fashion. Check it out for yourself. I see your idea, I'm just unsure why you wanted to use the RETURN statement like this.

Semper fi, Gunnery Sergeant Fowler...

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

CODO ERGO SUM

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-18 : 14:41:10

Col. Jones,

Yep, works like a charm! Also, I thought that was a great way to save processing time by hard-coding something as quantifiably static as the number of minutes in a day. I like the way this works!

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-18 : 16:09:51

Of course, this is even shorter without the date functions, just simple math using the modulo operator:


drop function dbo.fn_ConvertMinutes
go
create function dbo.fn_ConvertMinutes(@Minutes int)
returns	  varchar(100)
as
begin
return
convert(varchar(100), @Minutes/1440)+' Days ' +
convert(varchar(100),(@Minutes%1440)/60)+ ' Hours ' +
convert(varchar(100), @Minutes%60)+ ' Minutes'
end
go
select Days_Mours_Minutes = dbo.fn_ConvertMinutes(2870)

CODO ERGO SUM

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-21 : 13:59:09

This is even better! But what really jumps out at me in this example is the use of that hybrid temporal unit known as 'Mours'

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-21 : 14:18:46

I only use Mours during testing.

quote:
Originally posted by Xerxes

This is even better! But what really jumps out at me in this example is the use of that hybrid temporal unit known as 'Mours'

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

CODO ERGO SUM

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-21 : 14:24:51

Too funny, Colonel Jones!

Thanks for the laugh! (and the cool modulo version!)

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources