I Hate Dates (and Prunes, too!)

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)

I Hate Dates (and Prunes, too!)

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2010-06-21 : 18:03:17

I have code that combines the data from 3 tables to create a new one called CURRENT. In this process, a new column called "Days" is to be calculated depending on the dates two input columns. Id the result is POSITIVE, then the result of the computation is accepted, else a ZERO is the value for "Days". Unfortunately, my function for these two dates aren't working...here's a simplified example:

.....the code............
SELECT a.track
, a.event_date
, c.max_dt
, fnMax(0, CAST(a.event_date AS int) - CAST(c.max_dt AS int)) AS Days
INTO #Y_TEST
from [PLF].[dbo].[Events] a
, [PLF].[dbo].[Other] b
, [PLF].[dbo].[Tix] c
where a.event_main_id = b.event_main_id
and b.track = c.track
and b.event_id = c.event_id
GO

and my function........
CREATE FUNCTION [dbo].[fnMax](@p1 INT,@p2 INT)
RETURNS IN
AS BEGIN
DECLARE @Result INT
SET @p2=COALESCE(@p2,@p1)
SELECT
@Result = (SELECT CASE WHEN @p1>@p2 THEN @p1 ELSE @p2 END)
RETURN @Result
END

Any help you can offer is much appreciated!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-21 : 18:28:08

Why are you casting the dates as integers? Why aren't you using the DateDiff() function?

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2010-06-21 : 21:31:31

Because datediff() gives an absolute value. I have to know if the event_date is less than the max_dt--if it is negative, then I need to put a zero in Days.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-21 : 22:18:04

[code]
SELECT a.track
, a.event_date
, c.max_dt
~~, fnMax(0, CAST(a.event_date AS int) - CAST(c.max_dt AS int)) AS Days~~
, case when a.event_date > c.max_dt then datediff(day, c.max_dt, a.event_date) else 0 end AS Days
INTO #Y_TEST
FROM [PLF].[dbo].[Events] a
JOIN [PLF].[dbo].[Other] b ON a.event_main_id = b.event_main_id
JOIN [PLF].[dbo].[Tix] c ON b.track = c.track
[/code]

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

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2010-06-22 : 12:15:50

Thanks, khtan!!!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-23 : 03:33:04

quote:
Originally posted by Xerxes

Thanks, khtan!!!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

Probably you will now love Dates (and Prunes, too)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources