help with 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 2005 Forums

Transact-SQL (2005)

help with function

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-10 : 03:16:54

Hi,

I have the following VB.NET function below, and I am trying to use it in SQL. My TSQL isnt very good and I'm trying to figure out the best way to convert it.

Is there a much simpler way to do this in TSQL ? Any built in functions I should make use of ?

Due to the way the query I am setting up is designed, I'm not really able to bring the date as a seperate value like I do 99.9% of the time, which would make it much much easier.

Any help is much appreciated!

Thanks again,
mike123

Public Function show_ShortTime(ByVal lastPost As DateTime) As String

Dim showLastPostDiff_Minutes As Integer = CInt(DateDiff(DateInterval.Minute, lastPost, Date.Now))

Select Case showLastPostDiff_Minutes

Case Is < 1
Dim showLastPostDiff_Seconds As Integer = CInt(DateDiff(DateInterval.Second, lastPost, Date.Now))

Select Case showLastPostDiff_Seconds
Case Is < 15
show_ShortTime = "(.. seconds ago)"
Case Else
show_ShortTime = "(" & showLastPostDiff_Seconds.ToString & " seconds ago)"
End Select

Case Is <= 60
show_ShortTime = "(" & showLastPostDiff_Minutes.ToString & " mins ago)"
Case Is < 1440
Dim hours As Integer = CInt(showLastPostDiff_Minutes / 60)
Dim minutes As Integer = CInt(showLastPostDiff_Minutes Mod 60)

If hours > 1 Then
show_ShortTime = "(" & hours.ToString & " hrs ago)"
Else
show_ShortTime = "(" & hours.ToString & " hr ago)"

End If
Case Else
Dim days As Integer = CInt(showLastPostDiff_Minutes / (60 * 24))

If days > 1 Then
show_ShortTime = "(" & days.ToString & " days ago)"
Else
show_ShortTime = "(" & days.ToString & " day ago)"
End If

End Select

End Function

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 03:27:27

you need to use DATEDIFF function in t-sql for doing this. you also have CASE WHEN... expression to do conditional value return

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-10 : 08:29:08

Here is a function which outputs days hours minutes and seconds. You could change if you just want days or hours etc:

CREATE FUNCTION [dbo].[dateDiffference] (@DateIn datetime)
-----------------------------------------------------------
--USAGE:
--SELECT [dbo].[dateDiffference] ('11 March 2009 12:30:00')
--------------------------------------------------------------
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @SecDiff INT
DECLARE @MinDiff INT
DECLARE @HrDiff INT
DECLARE @RemainingSec INT
DECLARE @RemainingMin INT
DECLARE @RemainingHr INT
DECLARE @DayDiff INT
DECLARE @str varchar (1000)

SET @SecDiff =(SELECT DateDiff(second,getdate(),@DateIn))
SET @MinDiff =(SELECT DateDiff(minute,getdate(),@DateIn))
SET @HrDiff =(SELECT DateDiff(hour,getdate(),@DateIn))
SET @DayDiff =(SELECT DateDiff(day,getdate(),@DateIn))

If @MinDiff>0
set @RemainingSec = @MinDiff - (@SecDiff/60)
else
set @RemainingSec =@SecDiff

If @HrDiff >0
set @RemainingMin = @HrDiff - (@MinDiff/60)
else
set @RemainingMin =@MinDiff

If @DayDiff >0
set @RemainingHr = @DayDiff - (@HrDiff/60)
else
set @RemainingHr =@HrDiff

set @str = convert(varchar,@DayDiff) + ' days, '
set @str = @str + convert(varchar,@RemainingHr) + ' hours, '
set @str = @str + convert(varchar,@RemainingMin) + ' mins, '
set @str = @str + convert(varchar,@RemainingSec) + ' secs to go'

RETURN @str
END
GO

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-03-11 : 20:50:34

Hey DarkDusky,

That helps alot, appreciated. I really suck at TSQL and am trying to modify it a bit.

My function won't compile, becuase of syntax errors..

Any idea where I am going completely wrong ? Can I not do CASE statements inside CASE statements ?

any help much appreciated!!
thanks again,
mike123

create FUNCTION [dbo].[dateDifference] (@DateIn datetime)
-----------------------------------------------------------
--USAGE:
--SELECT [dbo].[dateDiffference] ('11 March 2009 12:30:00')
--------------------------------------------------------------
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @SecDiff INT
DECLARE @MinDiff INT
DECLARE @HrDiff INT
DECLARE @RemainingSec INT
DECLARE @RemainingMin INT
DECLARE @RemainingHr INT
DECLARE @DayDiff INT
DECLARE @str varchar (1000)

SET @SecDiff =(SELECT DateDiff(second,getdate(),@DateIn))
SET @MinDiff =(SELECT DateDiff(minute,getdate(),@DateIn))
SET @HrDiff =(SELECT DateDiff(hour,getdate(),@DateIn))
SET @DayDiff =(SELECT DateDiff(day,getdate(),@DateIn))

CASE

WHEN @MinDiff < 1 THEN

CASE
WHEN @SecDiff < 15 THEN
SET @str = '(.. seconds ago)'
ELSE
SET @str = '(' + @SecDiff + ' seconds ago)'
END

WHEN @MinDiff <= 60 THEN

SET @str = '(.. seconds ago)'

WHEN @MinDiff < 1440 THEN

SET @str = '(.. seconds ago)'

ELSE
SET @str = '(.. seconds ago)'
END

If @MinDiff>0
set @RemainingSec = @MinDiff - (@SecDiff/60)
else
set @RemainingSec =@SecDiff

If @HrDiff >0
set @RemainingMin = @HrDiff - (@MinDiff/60)
else
set @RemainingMin =@MinDiff

If @DayDiff >0
set @RemainingHr = @DayDiff - (@HrDiff/60)
else
set @RemainingHr =@HrDiff

--set @str = convert(varchar,@DayDiff) + ' days, '
--set @str = @str + convert(varchar,@RemainingHr) + ' hours, '
--set @str = @str + convert(varchar,@RemainingMin) + ' mins, '
--set @str = @str + convert(varchar,@RemainingSec) + ' secs to go'

----this is just temp below
--set @str = '2 hrs ago'

RETURN @str
END
GO

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-12 : 06:01:23

I'm not sure of the changes you want e.g. all the '..seconds ago'
RE: nesting
You may find it easier to nest IF ELSE statements. You can enclose sections inside BEGIN and END to mark them out but this is not essential.
e.g.
declare @a int
set @a=8
IF @a<10
...IF @a=9
......PRINT 'a=9'
...Else
......PRINT 'a is < 9'
Else
...PRINT 'a >=10'

--i've put ... to show tabs which were removed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 06:21:29

Mike, also see this T-SQL function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107275

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources