calling getDate in UDF
-
While calling getdate in UDF i am getting error..
Here is My code
======================
if @Enddate> getdate
begin
select @Enddate= getdate()
end
Error Message
======================
Server: Msg 207, Level 16, State 3, Procedure GetMissingPrepaycount, Line 17
Invalid column name 'getdate'.
Server: Msg 207, Level 16, State 1, Procedure GetMissingPrepaycount, Line 20
Invalid column name 'getdate'.
-
1. IF @Enddate > getdate should be > GetDate()
2. I don't think you can call GetDate() from inside a UDF
Try and pass in GETDATE() as a 2nd Variable as in below:
CREATE FUNCTION Test
(
@EndDate DATETIME,
@getdate-2 DATETIME
)
RETURNS DATETIME
AS
BEGIN
IF @EndDate > @getdate-2
BEGIN
SET @EndDate = @getdate-2
END
RETURN @EndDate
END
GO
SELECT dbo.Test('2005-03-01', GETDATE())
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Piggy-backing on AJ, every now and then one can read, that you can workaround this "limitation" by utilizing a view. Now, consider this:
USE northwind
GO
CREATE VIEW foolview
AS
SELECT
GETDATE() AS Jetzt
GO
CREATE FUNCTION fool_me()
RETURNS DATETIME
AS
BEGIN
RETURN (
SELECT
Jetzt
FROM
foolview
 
END
GO
CREATE function you_dont_fool_me(@Jetzt datetime)
RETURNS DATETIME
AS
BEGIN
RETURN @Jetzt
END
GO
DECLARE @Jetzt datetime
SET @Jetzt = GETDATE()
--Test 1 viele Zeilen
SELECT DISTINCT
dbo.fool_me()
FROM
[Order Details] AS od
INNER JOIN
Orders AS o
ON
o.OrderId = od.OrderID
--Test2 eine Zeile
SELECT DISTINCT
dbo.you_dont_fool_me(@Jetzt)
FROM
[Order Details] AS od
INNER JOIN
Orders AS o
ON
o.OrderId = od.OrderID
GO
DROP FUNCTION fool_me
DROP FUNCTION you_dont_fool_me
DROP VIEW foolview
------------------------------------------------------
2005-02-28 15:00:59.780
2005-02-28 15:00:59.790
2005-02-28 15:00:59.710
2005-02-28 15:00:59.810
2005-02-28 15:00:58.497
2005-02-28 15:00:59.600
2005-02-28 15:00:59.610
2005-02-28 15:00:59.430
2005-02-28 15:00:58.960
2005-02-28 15:00:58.970
2005-02-28 15:00:59.420
2005-02-28 15:00:59.140
2005-02-28 15:00:59.150
2005-02-28 15:00:59.350
2005-02-28 15:00:59.500
2005-02-28 15:00:58.990
2005-02-28 15:00:59.000
2005-02-28 15:00:59.520
2005-02-28 15:00:58.860
2005-02-28 15:00:58.737
2005-02-28 15:00:58.747
2005-02-28 15:00:59.630
2005-02-28 15:00:59.640
2005-02-28 15:00:58.387
2005-02-28 15:00:58.397
2005-02-28 15:00:58.880
2005-02-28 15:00:59.240
2005-02-28 15:00:59.250
2005-02-28 15:00:58.597
2005-02-28 15:00:59.070
2005-02-28 15:00:59.080
2005-02-28 15:00:59.880
2005-02-28 15:00:59.890
2005-02-28 15:00:58.790
2005-02-28 15:00:58.517
2005-02-28 15:00:58.467
2005-02-28 15:00:58.477
2005-02-28 15:00:59.430
2005-02-28 15:00:59.440
2005-02-28 15:00:59.510
2005-02-28 15:00:59.520
2005-02-28 15:00:58.707
2005-02-28 15:00:59.060
2005-02-28 15:00:58.547
2005-02-28 15:00:58.870
2005-02-28 15:00:59.710
2005-02-28 15:00:58.397
2005-02-28 15:00:58.407
2005-02-28 15:00:59.700
2005-02-28 15:00:59.400
2005-02-28 15:00:59.250
2005-02-28 15:00:59.170
2005-02-28 15:00:59.320
2005-02-28 15:00:58.587
2005-02-28 15:00:59.890
2005-02-28 15:00:59.900
2005-02-28 15:00:59.870
2005-02-28 15:00:59.940
2005-02-28 15:00:58.657
2005-02-28 15:00:58.667
2005-02-28 15:00:59.760
2005-02-28 15:00:59.770
2005-02-28 15:00:59.440
2005-02-28 15:00:58.980
2005-02-28 15:00:58.990
2005-02-28 15:00:59.260
2005-02-28 15:00:59.580
2005-02-28 15:00:59.590
2005-02-28 15:00:58.407
2005-02-28 15:00:58.577
2005-02-28 15:00:58.587
2005-02-28 15:00:58.900
2005-02-28 15:00:59.700
2005-02-28 15:00:58.717
2005-02-28 15:00:58.727
2005-02-28 15:00:59.690
2005-02-28 15:00:59.370
2005-02-28 15:00:59.860
2005-02-28 15:00:59.870
2005-02-28 15:00:59.090
2005-02-28 15:00:59.160
2005-02-28 15:00:59.020
2005-02-28 15:00:58.840
2005-02-28 15:00:58.850
2005-02-28 15:00:59.010
2005-02-28 15:00:58.920
2005-02-28 15:00:58.647
2005-02-28 15:00:58.767
2005-02-28 15:00:58.697
2005-02-28 15:00:58.707
2005-02-28 15:00:59.450
2005-02-28 15:00:59.460
2005-02-28 15:00:59.610
2005-02-28 15:00:58.487
2005-02-28 15:00:58.497
2005-02-28 15:00:59.110
2005-02-28 15:00:59.270
2005-02-28 15:00:58.567
2005-02-28 15:00:58.577
2005-02-28 15:00:58.417
2005-02-28 15:00:59.530
2005-02-28 15:00:59.680
2005-02-28 15:00:59.340
2005-02-28 15:00:59.190
2005-02-28 15:00:59.200
2005-02-28 15:00:59.790
2005-02-28 15:00:58.930
2005-02-28 15:00:58.940
2005-02-28 15:00:59.080
2005-02-28 15:00:59.090
2005-02-28 15:00:58.687
2005-02-28 15:00:58.697
2005-02-28 15:00:59.000
2005-02-28 15:00:59.010
2005-02-28 15:00:58.850
2005-02-28 15:00:58.860
2005-02-28 15:00:58.527
2005-02-28 15:00:58.537
2005-02-28 15:00:59.390
2005-02-28 15:00:59.470
2005-02-28 15:00:58.457
2005-02-28 15:00:58.467
2005-02-28 15:00:59.460
2005-02-28 15:00:59.490
2005-02-28 15:00:59.500
2005-02-28 15:00:59.920
2005-02-28 15:00:59.950
2005-02-28 15:00:59.280
2005-02-28 15:00:59.290
2005-02-28 15:00:58.537
2005-02-28 15:00:58.820
2005-02-28 15:00:58.830
2005-02-28 15:00:59.100
2005-02-28 15:00:59.110
2005-02-28 15:00:59.750
2005-02-28 15:00:58.637
2005-02-28 15:00:58.647
2005-02-28 15:00:59.740
2005-02-28 15:00:59.820
2005-02-28 15:00:59.830
2005-02-28 15:00:59.670
2005-02-28 15:00:59.840
2005-02-28 15:00:59.850
2005-02-28 15:00:59.200
2005-02-28 15:00:59.210
2005-02-28 15:00:59.310
2005-02-28 15:00:59.030
2005-02-28 15:00:59.180
2005-02-28 15:00:59.560
2005-02-28 15:00:58.427
2005-02-28 15:00:58.747
2005-02-28 15:00:58.757
2005-02-28 15:00:58.950
2005-02-28 15:00:59.770
2005-02-28 15:00:59.930
2005-02-28 15:00:59.130
2005-02-28 15:00:59.290
2005-02-28 15:00:58.777
2005-02-28 15:00:59.590
2005-02-28 15:00:58.627
2005-02-28 15:00:59.860
2005-02-28 15:00:59.660
2005-02-28 15:00:59.670
2005-02-28 15:00:59.850
2005-02-28 15:00:58.830
2005-02-28 15:00:59.210
2005-02-28 15:00:59.360
2005-02-28 15:00:59.370
2005-02-28 15:00:59.470
2005-02-28 15:00:59.020
2005-02-28 15:00:58.507
2005-02-28 15:00:58.517
2005-02-28 15:00:58.357
2005-02-28 15:00:58.367
2005-02-28 15:00:58.437
2005-02-28 15:00:58.447
2005-02-28 15:00:58.667
2005-02-28 15:00:58.677
2005-02-28 15:00:58.910
2005-02-28 15:00:59.550
2005-02-28 15:00:59.560
2005-02-28 15:00:59.620
2005-02-28 15:00:58.367
2005-02-28 15:00:58.377
2005-02-28 15:00:58.617
2005-02-28 15:00:58.627
2005-02-28 15:00:58.940
2005-02-28 15:00:58.757
2005-02-28 15:00:58.767
2005-02-28 15:00:59.650
2005-02-28 15:00:58.477
2005-02-28 15:00:58.487
2005-02-28 15:00:58.377
2005-02-28 15:00:59.120
2005-02-28 15:00:58.800
2005-02-28 15:00:58.810
2005-02-28 15:00:59.910
2005-02-28 15:00:59.300
2005-02-28 15:00:59.310
2005-02-28 15:00:59.900
2005-02-28 15:00:58.557
2005-02-28 15:00:59.050
2005-02-28 15:00:59.060
2005-02-28 15:00:59.220
2005-02-28 15:00:59.230
2005-02-28 15:00:59.540
2005-02-28 15:00:59.550
2005-02-28 15:00:59.410
2005-02-28 15:00:59.730
2005-02-28 15:00:58.447
2005-02-28 15:00:59.480
2005-02-28 15:00:59.490
2005-02-28 15:00:58.677
2005-02-28 15:00:58.687
2005-02-28 15:00:59.330
2005-02-28 15:00:59.800
2005-02-28 15:00:59.810
2005-02-28 15:00:59.640
2005-02-28 15:00:59.650
2005-02-28 15:00:59.910
2005-02-28 15:00:58.970
2005-02-28 15:00:58.980
2005-02-28 15:00:58.810
2005-02-28 15:00:58.820
2005-02-28 15:00:59.750
2005-02-28 15:00:59.830
2005-02-28 15:00:59.150
2005-02-28 15:00:59.380
2005-02-28 15:00:58.727
2005-02-28 15:00:59.040
2005-02-28 15:00:59.050
2005-02-28 15:00:58.890
2005-02-28 15:00:58.900
2005-02-28 15:00:59.720
2005-02-28 15:00:59.730
2005-02-28 15:00:59.570
2005-02-28 15:00:58.607
2005-02-28 15:00:59.230
(238 row(s) affected)
------------------------------------------------------
2005-02-28 15:00:58.307
(1 row(s) affected)
Not only will you get too much rows back, but it also shows nicely that a scalar UDF operates on a row-by-row basis.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply