http://sqlserver2000.databases.aspfaq.com/how-do-i-use-getdate-within-a-user-defined-function-udf.html
saya copas: How do I use GETDATE() within a User-Defined Function (UDF)? SQL Server 2000 added the support for user-defined functions, but there are a few limitations which can be roadblocks at first. One is that you cannot use a non-deterministic function within a UDF, e.g. GETDATE(). So, let's say you are trying to create a function that returns this moment, but a day in the future (e.g. tomorrow at this exact time). You would think about it this way: CREATE FUNCTION dbo.addDay() RETURNS DATETIME AS BEGIN DECLARE @dt DATETIME SET @dt = DATEADD(DAY, 1, GETDATE()) RETURN @dt END But you will get this error message: Server: Msg 443, Level 16, State 1, Procedure addDay, Line 6 Invalid use of 'getdate' within a function. So, instead, you should handle it so that you pass non-deterministic values into the function, for example: CREATE FUNCTION dbo.addDay ( @dt DATETIME ) RETURNS DATETIME AS BEGIN SET @dt = DATEADD(DAY, 1, @dt) RETURN @dt END Then you can call it like this: DECLARE @dt DATETIME SET @dt = GETDATE() -- or CURRENT_TIMESTAMP SELECT dbo.addDay(GETDATE()) Another idea is to use OPENQUERY as follows: CREATE FUNCTION dbo.addDay() RETURNS DATETIME AS BEGIN DECLARE @dt DATETIME SELECT @dt = dt + 1 FROM OPENQUERY ( YourServer, 'SELECT dt = GETDATE()' ) RETURN @dt END GO If YourServer is not configured for Data Access, you will get this error: Server: Msg 7411, Level 16, State 1, Procedure addDay, Line 6 Server 'YourServer' is not configured for DATA ACCESS. You will need to set the local linked server to allow data access in order to accept calls through OPENQUERY. You can do this using sp_serveroption: EXEC sp_serveroption 'YourServer', 'DATA ACCESS', TRUE A less efficient (and less accurate) workaround is to use an intermediate view. This simplifies your coding a bit, since you no longer have to pass GETDATE() into the function. First, create the view: CREATE VIEW dbo.vGETDATE AS BEGIN SELECT vGETDATE = GETDATE() END Then, create the function: CREATE FUNCTION dbo.addDay() RETURNS DATETIME AS BEGIN DECLARE @dt DATETIME SELECT @dt = [vgetdate]+1 FROM vGETDATE RETURN @dt END And you can call it like this: PRINT dbo.addDay() However, this has been proven to produce inconsistent results, such as this issue that Tibor Karaszi has pointed out: USE Northwind GO CREATE VIEW dbo.vCurrentDateTime AS SELECT gd = GETDATE() GO CREATE FUNCTION dbo.getFromView() RETURNS DATETIME AS BEGIN RETURN (SELECT gd FROM vCurrentDateTime) END GO CREATE FUNCTION dbo.getFromSelf() ( @dt DATETIME ) RETURNS DATETIME AS BEGIN RETURN @dt END GO DECLARE @dt datetime SET @dt = GETDATE() SELECT DISTINCT dbo.getFromView() FROM Orders o INNER JOIN [Order Details] od ON o.OrderId = od.OrderID SELECT DISTINCT dbo.getFromSelf(@dt) FROM Orders o INNER JOIN [Order Details] od ON o.OrderId = od.OrderID GO DROP FUNCTION dbo.getFromView(), dbo.getFromSelf() DROP VIEW vCurrentDateTime On my machine, the first query produced anywhere from 8 to 10 rows (and this changed with every refresh), while the second query *always* produced exactly one. This is because using a non-deterministic function within the view leads to GETDATE() being evaluated multiple times as the query runs. Now, this is a contrived example, and is unlikely to resemble anything you're doing in the real world; however, I wanted to include this discrepancy for completeness, and in support of using other methods to getting GETDATE() passed into your function. My preference is to use a local variable and pass it into the function from the calling script... The difference between a deterministic and non-deterministic function is that the former always produces the same output (given the same input), whereas the latter can produce a different output each time they are called (again, with the same input). This is why system functions like GETDATE() are non-deterministic; they will produce different results each time you call them. In Books Online, there is a topic called 'Deterministic and Nondeterministic Funct...' which goes into much more detail about this issue, and lists the inbuilt functions organized by whether they are always, sometimes, or never deterministic. 2010/4/16 MBA <[email protected]> > > > sejauh pengalaman saya selama ini. > penggunaan secara langsung di UDF memang belum pernah berhasil bang. > > jadi memang perlu satu perantara dulu. > dalam kasus abang bisa dituliskan > > declare @param2 Datetime > > if @param2 IS NULL > @param2 = (select tCurDate from v_Date) > > > v_Date tidak membebani performance bang, karena hanya satu baris satu > record. > > > Salam Hangat dan Jabat Erat, > > *MBA* > > [When you eliminate the impossible, then whatever is left, no matter how > improbable, must be the truth] > > > ------------------------------ > *From:* [email protected] [mailto: > [email protected]] *On Behalf Of *Haer Talib > *Sent:* Friday, April 16, 2010 14:59 > > *To:* [email protected] > *Subject:* Re: [belajar-access] Datetime di SQL > > > > Bang MBA, > > Adakah cara agar tdk perlu create v_Date dulu? > Sebenarnya saya hanya perlu memberi nilai current date untuk parameter yang > kosong/null. > semacam ini (dalam udf): > > if @param2 IS NULL > @param2 = GETDATE() > > nah, getdate() di situ yg error. > > Thanks, > Haer > > > 2010/4/16 MBA <[email protected]> > >> Mohon ijin ikutan jawab. >> >> salah satu cara kita bisa pake pertolongan view - karena di view fungsi >> getdate() bisa dipakai. >> >> misalnya sbb: >> CREATE VIEW V_DATE >> AS >> SELECT GETDATE() TCURDATE >> >> Trus dari UDF tinggal dipanggil sbb: >> >> declare @tCurDate >> set @tCurDate=(select tCurDate from v_Date) >> >> >> >> Salam Hangat dan Jabat Erat, >> MBA >> [When you eliminate the impossible, then whatever is left, no matter how >> improbable, must be the truth] >> >> >> >> ________________________________ >> >> From: [email protected] [mailto: >> [email protected]] >> On Behalf Of Haer Talib >> Sent: Friday, April 16, 2010 14:32 >> To: [email protected] >> Subject: [belajar-access] Datetime di SQL >> >> >> >> >> Halah si abang juga lupa ngedit subject... >> >> Sekalian nih bang, >> >> apa alternatif pengganti GETDATE() di dalam UDF? Soalnya kalau pakai >> GETDATE() mesti error. >> >> Thanks, >> Haer >> >> >> 2010/4/16 Aksan Kurdin <[email protected] >> <mailto:[email protected]> > >> >> >> ? >> >> >> Buat UDF ini di SQL Server: >> >> CREATE >> >> FUNCTION [dbo].[fn_GetTime]( >> @DateToTime datetime >> ) >> RETURNS varchar(10) >> AS >> BEGIN >> RETURN CONVERT (varchar, @DateToTime, 108) >> END >> >> >> >> lalu jalankan dengan script seperti: >> >> SELECT Nama, dbo.fn_GetTime(JamMulai) AS JamMulai FROM namaTabel >> >> aksan kurdin >> >> >> >> >> ----- Original Message ----- >> From: GALIH PERSADHA <mailto:[email protected]> >> To: [email protected] >> <mailto:[email protected]> >> Sent: Friday, April 16, 2010 8:20 AM >> Subject: Bls: [belajar-access] Mohon saran dan masukan >> >> >> >> >> mohon bantuannya >> >> pada sql server yang bertipe datetime saya mempunyai data >> sbb : >> >> Nama JamMulai >> Galih 1899-12-30 08:15:00.000 >> Pandu 1899-12-30 09:15:00.000 >> >> permasalahan saya bagaimana cara mengambil datanya dengan >> sebuah kriteria sehingga yang akan dimunculkan adalah jamnya saja >> >> contohnya : >> kriteria yang diberikan adalah 08:15:00 >> >> sehingga yang tampil adalah : >> >> Nama JamMulai >> Galih 08:15:00 >> >> >> mohon pencerahannya. terimakasih >> >> >> >> __________________________________________________ >> Apakah Anda Yahoo!? >> Lelah menerima spam? Surat Yahoo! memiliki perlindungan >> terbaik terhadap spam >> http://id.mail.yahoo.com <http://id.mail.yahoo.com> >> >> >> >> >> >> >> -- >> >> Jadikan www.gapura.web.id <http://www.gapura.web.id> sebagai >> homepage browser anda. >> => Akses ke website favorit tinggal klik aja. (Lihat dulu deh). >> >> >> >> >> >> No virus found in this incoming message. >> Checked by AVG - www.avg.com >> Version: 9.0.801 / Virus Database: 271.1.1/2812 - Release Date: 04/16/10 >> 01:31:00 >> >> >> >> >> >> ------------------------------------ >> >> SPAM IS PROHIBITEDYahoo! Groups Links >> >> >> >> > > > -- > > Jadikan www.gapura.web.id sebagai homepage browser anda. > => Akses ke website favorit tinggal klik aja. (Lihat dulu deh). > > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 9.0.801 / Virus Database: 271.1.1/2812 - Release Date: 04/16/10 > 01:31:00 > > -- Aksan Kurdin
