Getdate() in UDF column workaround

4 Mar 2006 10:08 PM

AkAlan

I have a column that needs to display the number of Status hours between
Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am using
an Access project as my front end and SQL Server 2000 as my back end. I have
tried using the following as a row source in my function:
CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE +
STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END

I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
like that. How can I display the status time on my form? I was thinking maybe
the text box record source could be a select statement but not sure how to
write it, any ideas?

4 Mar 2006 10:19 PM

Roy Harvey

DateDiff(HH, STATUS_START_DATE + STATUS_START_TIME,
COALESCE(STATUS_STOP_DATE + STATUS_STOP_TIME,
GETDATE())

Roy

On Sat, 4 Mar 2006 14:08:27 -0800, AkAlan
<AkA***@discussions.microsoft.com> wrote:

Show quote

>I have a column that needs to display the number of Status hours between
>Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am using
>an Access project as my front end and SQL Server 2000 as my back end. I have
>tried using the following as a row source in my function:
>CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE +
>STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
>STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
>
>I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
>like that. How can I display the status time on my form? I was thinking maybe
>the text box record source could be a select statement but not sure how to
>write it, any ideas?

5 Mar 2006 6:42 AM

Uri Dimant

Hi
CREATE FUNCTION dbo.Get_Getdate
(@dt DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN @dt
END

SELECT dbo.Get_Getdate (GETDATE())
SELECT dbo.Get_Getdate ('20050101')

Show quote

"AkAlan" <AkA***@discussions.microsoft.com> wrote in message
news:30B29109-1B8E-4716-A506-EEDB943F4B64@microsoft.com...
>I have a column that needs to display the number of Status hours between
> Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am
> using
> an Access project as my front end and SQL Server 2000 as my back end. I
> have
> tried using the following as a row source in my function:
> CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE
> +
> STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
> STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
>
> I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
> like that. How can I display the status time on my form? I was thinking
> maybe
> the text box record source could be a select statement but not sure how
> to
> write it, any ideas?