1) Am I right in believing I can't access the GetDate() function from within
a table valued UDF ... ? That seems to be what the syntax checker is
telling me. Any suggested workarounds ?
2) I tried create a view which used a table valued UDF, with " getdate() "
as a parameter, in the view's from clause and the system didn't like that
either ... Any suggestions ? Are there ways of parameterizing a view ?
3) I need the view construct because I need to reference the returned
dataset from Analysis Services as a dimension. I prefer the UDF construct
(as opposed to selecting off a table with a where clause) because I don't
have to build a process to add new date records to the hypothetical table.
Performance isn't really an issue, because the only place the view is invoke
d
is in processing a cube in Analysis Services.
4) Some SQL
...
CREATE FUNCTION dbo.tfun_Date (
@.EndDate smalldatetime) --added when getdate didn't work
RETURNS @.DateTable table (
DateValue smalldatetime )
BEGIN
Declare @.DateIdx smalldatetime
Declare @.StartDate smalldatetime
Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
Set @.DateIdx = @.StartDate
--Set @.EndDate = getdate()
While @.DateIdx <= @.EndDate
Begin
Insert @.DateTable (DateValue) values (@.DateIdx)
Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
End
Return
END
--
CREATE VIEW dbo.v_date
AS
SELECT top 10000 DateValue
, DateYYYY = Datepart(yyyy, DateValue)
, DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
, DateQtr = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN 'Q1'
WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN 'Q3' WHEN 8
THEN
'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN 11 THEN
'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
, DateMM = Datepart(mm, DateValue)
, DateMMAbbrev = CASE Datepart(mm, DateValue)
WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7 THEN 'Jul' WHEN
8 THEN 'Aug' WHEN 9 THEN 'Sep' WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
, DateDD = Datepart(dd,
DateValue), DowNbr = Datepart(dw, DateValue)
, DowAbbr = CASE Datepart(dw, DateValue)
WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN 'Wed'
WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7 THEN 'Sun' ELSE
'Err' END
, DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
, WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 7 - Datepart(dw
,
DateValue)) / 7
, WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue), DateValue)
, Workday = Case
When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
Else 'Workday' End
, HolidayName
, PublicHolidayFlag
--THIS IS THE BOGUS LINE
FROM tfun_date( getdate() ) D
--ENDS HERE
Left outer Join ZR_PublicHols on D.DateValue = ZR_PublicHols.HolidayDate
order by DateValueYou could pass in to the UDF a new parameter which when called, you send it
the GETDATE/CURRENTTIMESTAMP function.
Then just use that new parameter as your getdate()
hth
Eric
MarcusW wrote:
> 1) Am I right in believing I can't access the GetDate() function from
> within a table valued UDF ... ? That seems to be what the syntax
> checker is telling me. Any suggested workarounds ?
> 2) I tried create a view which used a table valued UDF, with "
> getdate() " as a parameter, in the view's from clause and the system
> didn't like that either ... Any suggestions ? Are there ways of
> parameterizing a view ?
> 3) I need the view construct because I need to reference the returned
> dataset from Analysis Services as a dimension. I prefer the UDF
> construct (as opposed to selecting off a table with a where clause)
> because I don't have to build a process to add new date records to
> the hypothetical table. Performance isn't really an issue, because
> the only place the view is invoked is in processing a cube in
> Analysis Services.
> 4) Some SQL
> ...
> CREATE FUNCTION dbo.tfun_Date (
> @.EndDate smalldatetime) --added when getdate didn't work
> RETURNS @.DateTable table (
> DateValue smalldatetime )
> BEGIN
> Declare @.DateIdx smalldatetime
> Declare @.StartDate smalldatetime
> Set @.StartDate = dbo.sfun_getdateparmref('DateDim1Start')
> Set @.DateIdx = @.StartDate
> --Set @.EndDate = getdate()
> While @.DateIdx <= @.EndDate
> Begin
> Insert @.DateTable (DateValue) values (@.DateIdx)
> Set @.DateIdx = DateAdd(dd,1, @.DateIdx)
> End
> Return
> END
> --
> CREATE VIEW dbo.v_date
> AS
> SELECT top 10000 DateValue
> , DateYYYY = Datepart(yyyy, DateValue)
> , DateYYAbbrev = RIGHT(CONVERT(Char(4), Datepart(yy, DateValue)), 2)
> , DateQtr = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Q1' WHEN 2 THEN 'Q1' WHEN 3 THEN
> 'Q1' WHEN 4 THEN 'Q2' WHEN 5 THEN 'Q2' WHEN 6 THEN 'Q2' WHEN 7 THEN
> 'Q3' WHEN 8 THEN
> 'Q3' WHEN 9 THEN 'Q3' WHEN 10 THEN 'Q4' WHEN
> 11 THEN 'Q4' WHEN 12 THEN 'Q4' ELSE 'Er' END
> , DateMM = Datepart(mm, DateValue)
> , DateMMAbbrev = CASE Datepart(mm, DateValue)
> WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN
> 'Mar' WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7
> THEN 'Jul' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep'
> WHEN 10 THEN 'Oct'
> WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE 'Err' END
> , DateDD = Datepart(dd,
> DateValue), DowNbr = Datepart(dw, DateValue)
> , DowAbbr = CASE Datepart(dw, DateValue)
> WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN
> 'Wed' WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' WHEN 7
> THEN 'Sun' ELSE 'Err' END
> , DayOfYear = DateDiff(d, CONVERT(smalldatetime, CONVERT(char(4),
> Datepart(yyyy, DateValue)) + '01' + '01', 112), DateValue) + 1
> , WeekOfYear = 1 + (DateDiff(d, CONVERT(smalldatetime,
> CONVERT(char(4), Datepart(yyyy, DateValue)) + '01' + '01', 112),
> DateValue) + 7 - Datepart(dw, DateValue)) / 7
> , WeekOfYearMondayDate = dateadd(dd, 1 - Datepart(dw, DateValue),
> DateValue) , Workday = Case
> When Datepart(dw, DateValue) > 5 then 'Weekend / Holiday'
> When PublicHolidayFlag = 'H' then 'Weekend / Holiday'
> Else 'Workday' End
> , HolidayName
> , PublicHolidayFlag
> --THIS IS THE BOGUS LINE
> FROM tfun_date( getdate() ) D
> --ENDS HERE
> Left outer Join ZR_PublicHols on D.DateValue =
> ZR_PublicHols.HolidayDate order by DateValue|||Marcus
> 1) Am I right in believing I can't access the GetDate() function from
within
> a table valued UDF ... ? That seems to be what the syntax checker is
> telling me. Any suggested workarounds ?
Correct. However, you can create a view that has the GetDate() in it and
then call that view from your UDF. This workaround may or may not continue
to work in future versions.
> 2) I tried create a view which used a table valued UDF, with " getdate() "
> as a parameter, in the view's from clause and the system didn't like that
> either ... Any suggestions ? Are there ways of parameterizing a view ?
An in-line table-valued UDF is a parameterized view. I think the problem
is still the GetDate() which cannot be a parameter to a UDF either. (Why
not? Because. Technically it is the issue of whether a function always
returns the same value or not.)
Russell Fields
Showing posts with label functions. Show all posts
Showing posts with label functions. Show all posts
Friday, March 23, 2012
Wednesday, March 21, 2012
getDate() Formatting and Functions Documentation
I'm trying to do something very simple here but I keep getting stuck becuase I can't find much on getDate() in the documenation. Where, in the documenation, does it talk about truncating times, adding to times and all that good stuf.
Below is what I'm trying to do here: I have a while loop that adds to the starting hour of 6am 15 min until it gets to like 7pm. I do realize at this point that just adding 15 is suppsed to add 15 days based on what I have read, but I'm getting an error when I parse this and since I can't seem to find the docs I don't know what to do next?
Msg 102, Level 15, State 1, Procedure PopulateDatabase, Line 32
Incorrect syntax near '@.TeeTime'.
set ANSI_NULLSONset QUOTED_IDENTIFIERONGO-- =============================================-- Author:Przemek-- Create date: -- Description:-- =============================================ALTER PROCEDURE [dbo].[PopulateDatabase]-- Add the parameters for the stored procedure hereASdeclare @.CourseIDuniqueIdentifierdeclare @.TeeTimeSlotintdeclare @.TeeTimedateTimeBEGINSET NOCOUNT ON;--Course 1 *******************************************************************INSERT INTOCourse (CourseID,Name, Address, PhoneNumber)VALUES(NewID(),'Prospect Lake','123 Prospect St', 2508129832)SET @.CourseID = (SELECT CourseIDFROM CourseWHERE Name ='Prospect Lake')SET @.TeeTimeSlot = 0SET @.TeeTime ='6:00'WHILE @.TeeTimeSlot < 56BEGIN INSERT INTOSchedule (ScheduleID, Course_FK, Date, TeeTime, NumberOfPlayers)VALUES(NewID(), @.CourseID,getDate(), @.TeeTime, Rand(5))@.TeeTime = @.TeeTime + 1ENDEND
Helloprzemeklach,
the problem should instead be on the next line, where you miss the SET keyword when incrementing @.TeeTime.
Documentation is into the Sql Books Online, that is the local sql server help. As for any other MS technology, you can find all the docs online as well, on the MSDN site. Here is a link:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/getstart_4fht.asp
HTH. -LV
Thanks, as my luck would have it I just figured this out like 2 min before you answered my post. Thanks for the link to the documentation, just what I was looking for.
I still have one more question. I want the column TeeTime to just store 06:00, 06:15 etc but instead it's filling it with 1900-01-01 06:00:00 etc. I know this is because of the smallDateTime datatype but is there a way to truncate this so the column is just filled with smallDateTime datatype but with just the time. If not, no big deal, I can just get my code to truncate the year/date as I use this data to populate controls.
set ANSI_NULLSONset QUOTED_IDENTIFIERONGO-- =============================================-- Author:Przemek-- Create date: 15 August 2006-- Description:Populates Course and Schedule-- tables with bogus data.-- =============================================ALTER PROCEDURE [dbo].[PopulateDatabase]-- Add the parameters for the stored procedure hereASdeclare @.CourseIDuniqueIdentifierdeclare @.TeeTimeSlotintdeclare @.TeeTimesmallDateTimeBEGINSET NOCOUNT ON;--Course 1 *******************************************************************INSERT INTOCourse (CourseID,Name, Address, PhoneNumber)VALUES(NewID(),'Prospect Lake','123 Prospect St', 2508129832)SET @.CourseID = (SELECT CourseIDFROM CourseWHERE Name ='Prospect Lake')SET @.TeeTimeSlot = 0SET @.TeeTime ='06:00:00'WHILE @.TeeTimeSlot < 56BEGIN INSERT INTOSchedule (ScheduleID, Course_FK, Date, TeeTime, NumberOfPlayers)VALUES(NewID(), @.CourseID,getDate(), @.TeeTime, ((Rand()*5)+1))SET @.TeeTime =DATEADD(minute, 15, @.TeeTime)SET @.TeeTimeSlot = @.TeeTimeSlot + 1ENDEND|||
przemeklach:
> I want the column TeeTime to just store 06:00, 06:15 etc but instead it's filling it with 1900-01-01 06:00:00 etc.
I'm afraid that's it. Both T-Sql and .NET languages don't have a 'time' data type. As you maybe implied, there's the DateTime.ToXYZString methods for handling display.
-LV
GETDATE in a function
I'm trying to make three functions, Today, Yesterday and Tomorrow. But I
can't, because you can't put GETDATE in a function.
1) Why not?
2) What can I do to make this work?
MauryHi
Yes , you cannot , however you are be able to pass GETDATE() function as a
parameter onto the UDF and later to operate with it
> I'm trying to make three functions, Today, Yesterday and Tomorrow
SELECT
DATEADD (d,DATEDIFF(D,0,GETDATE()-1),0) AS Yesterday ,
DATEADD (d,DATEDIFF(D,0,GETDATE()),0) AS Today,
DATEADD (d,DATEDIFF(D,0,GETDATE())+1,0) AS Tomorrow
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7B10973B-0A65-4E45-848C-441ADB34E8F0@.microsoft.com...
> I'm trying to make three functions, Today, Yesterday and Tomorrow. But I
> can't, because you can't put GETDATE in a function.
> 1) Why not?
> 2) What can I do to make this work?
> Maury|||"Uri Dimant" wrote:
> Yes , you cannot
Any idea why? I can't seem to think of any good reason for this.
> however you are be able to pass GETDATE() function as a
> parameter onto the UDF and later to operate with it
UDF?
> DATEADD (d,DATEDIFF(D,0,GETDATE()-1),0) AS Yesterday ,
Oh, I know how to do it mechanically, but it's precisely all that syntax
that I'm trying to avoid!
Maury|||Maury Markowitz wrote:
> "Uri Dimant" wrote:
>> Yes , you cannot
> Any idea why? I can't seem to think of any good reason for this.
>> however you are be able to pass GETDATE() function as a
>> parameter onto the UDF and later to operate with it
> UDF?
>> DATEADD (d,DATEDIFF(D,0,GETDATE()-1),0) AS Yesterday ,
> Oh, I know how to do it mechanically, but it's precisely all that syntax
> that I'm trying to avoid!
> Maury
>
Read this, it will explain all:
http://msdn.microsoft.com/msdnmag/issues/03/11/DataPoints/|||When a function is executed, it has to know exactly what data it will
operate on. Getdate() is a 'non-determinate' function since every time it
operates, it produces a different value.
'UDF' = user defined function. A prefix that some use in naming functions.
Also in popular usage is 'fn'.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:15F3B36E-49DD-4539-8B24-1DBE13AA3CC7@.microsoft.com...
> "Uri Dimant" wrote:
>> Yes , you cannot
> Any idea why? I can't seem to think of any good reason for this.
>> however you are be able to pass GETDATE() function as a
>> parameter onto the UDF and later to operate with it
> UDF?
>> DATEADD (d,DATEDIFF(D,0,GETDATE()-1),0) AS Yesterday ,
> Oh, I know how to do it mechanically, but it's precisely all that syntax
> that I'm trying to avoid!
> Maury
>|||"Arnie Rowland" wrote:
> When a function is executed, it has to know exactly what data it will
> operate on. Getdate() is a 'non-determinate' function since every time it
> operates, it produces a different value.
This doesn't really tell me WHY though. WHY does returning a different
datetime make it impossible to use in a function?
Consider this:
ALTER FUNCTION dbo.StartOfDay(@.date datetime) RETURNS datetime
BEGIN
RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
'/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar, DAY(@.date))))
END
This would be called like...
SELECT dbo.StartOfDay(GETDATE())
Now what is it about exactly that precudes this solution:
ALTER FUNCTION dbo.StartOfDay() RETURNS datetime
BEGIN
SELECT @.date = GETDATE()
RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
'/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar, DAY(@.date))))
END
The end result is exactly the same. The code that needs to run is exactly
the same. The actual execution is almost identical. So what is it about the
function creation mechanism inside SQL Server that makes this illegal?
Maury|||In your first example, the function is NOT passed in the function getdate(),
is it passed in the current returned value from getdate() -so therefore, the
function knows exactly the values it has to operate with.
In your second example, the function would not know the value of getdate()
until after it starts working -and that is, by design, not allowed.
Why does 0/1 create such a turmoil in math. I don't know 'exactly, but I
accept the it just isn't allowed.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14D32701-04DF-4A86-B680-6C1669715DEF@.microsoft.com...
> "Arnie Rowland" wrote:
>> When a function is executed, it has to know exactly what data it will
>> operate on. Getdate() is a 'non-determinate' function since every time it
>> operates, it produces a different value.
> This doesn't really tell me WHY though. WHY does returning a different
> datetime make it impossible to use in a function?
> Consider this:
> ALTER FUNCTION dbo.StartOfDay(@.date datetime) RETURNS datetime
> BEGIN
> RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
> '/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar,
> DAY(@.date))))
> END
> This would be called like...
> SELECT dbo.StartOfDay(GETDATE())
> Now what is it about exactly that precudes this solution:
> ALTER FUNCTION dbo.StartOfDay() RETURNS datetime
> BEGIN
> SELECT @.date = GETDATE()
> RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
> '/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar,
> DAY(@.date))))
> END
> The end result is exactly the same. The code that needs to run is exactly
> the same. The actual execution is almost identical. So what is it about
> the
> function creation mechanism inside SQL Server that makes this illegal?
> Maury|||User-defined functions are by definition "deterministic". A deterministic
function is one in which, for every value (or set of values) you pass into
it, it returns the same result. Examples of deterministic functions include
sine(), cosine(), etc.
Because of this limitation on determinism in user-defined functions, you are
not allowed to use non-deterministic functions inside your UDF's (like rand
or getdate). There are ways around this limitation, like creating a VIEW
composed of SELECT GETDATE(); and selecting from the VIEW from within your
UDF.
MS warns against relying on UDF's that circumvent determinism like this and
don't return the same results for the same set of values every time,
however.
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14D32701-04DF-4A86-B680-6C1669715DEF@.microsoft.com...
> "Arnie Rowland" wrote:
>> When a function is executed, it has to know exactly what data it will
>> operate on. Getdate() is a 'non-determinate' function since every time it
>> operates, it produces a different value.
> This doesn't really tell me WHY though. WHY does returning a different
> datetime make it impossible to use in a function?
> Consider this:
> ALTER FUNCTION dbo.StartOfDay(@.date datetime) RETURNS datetime
> BEGIN
> RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
> '/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar,
> DAY(@.date))))
> END
> This would be called like...
> SELECT dbo.StartOfDay(GETDATE())
> Now what is it about exactly that precudes this solution:
> ALTER FUNCTION dbo.StartOfDay() RETURNS datetime
> BEGIN
> SELECT @.date = GETDATE()
> RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
> '/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar,
> DAY(@.date))))
> END
> The end result is exactly the same. The code that needs to run is exactly
> the same. The actual execution is almost identical. So what is it about
> the
> function creation mechanism inside SQL Server that makes this illegal?
> Maury|||"Mike C#" wrote:
> Because of this limitation on determinism in user-defined functions
But why does this limitation exist? Is there a technical reason for this?
Everything I've seen so far says "you can't do it because we don't let you".
> or getdate). There are ways around this limitation, like creating a VIEW
> composed of SELECT GETDATE(); and selecting from the VIEW from within your
> UDF.
Which is baffling. If there is a real, technical, issue here, then this
shouldn't work either.
> MS warns against relying on UDF's that circumvent determinism like this and
> don't return the same results for the same set of values every time,
> however.
Do they give a reason anywhere? I've looked at the Transact-SQL docs, the
document someone posted here, and done a few googles on it too, but every
single case states the fact, and not the reason.
It's all rather maddening.
Maury|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:FA0444D2-F5B4-4CC2-820A-CB654C0455B0@.microsoft.com...
> "Mike C#" wrote:
> > Because of this limitation on determinism in user-defined functions
> But why does this limitation exist? Is there a technical reason for this?
> Everything I've seen so far says "you can't do it because we don't let
you".
One reason I SUSPECT is that in non-deterministic cases, things like
updates/inserts could cause wonkiness and violate ACID.
UPDATE FOO set entry_date=getdate()
All rows updated will have the exact same date/time, regardless of the
number of rows updated.
Now consider something like:
UPDATE BAR set entry_date=fn_mydate()
(assuming fn_mydate() basically is a user defined-function that returns
getdate() for simplicity's sake)
It may be harder for MS to guarantee that the optimizer only returns one
deterministic value for fn_mydate.
(worse case, every row gets a different date/time value.)
Now you've violated ACID.
This is just a guess on my part.
> > or getdate). There are ways around this limitation, like creating a
VIEW
> > composed of SELECT GETDATE(); and selecting from the VIEW from within
your
> > UDF.
> Which is baffling. If there is a real, technical, issue here, then this
> shouldn't work either.
> > MS warns against relying on UDF's that circumvent determinism like this
and
> > don't return the same results for the same set of values every time,
> > however.
> Do they give a reason anywhere? I've looked at the Transact-SQL docs, the
> document someone posted here, and done a few googles on it too, but every
> single case states the fact, and not the reason.
> It's all rather maddening.
> Maury
can't, because you can't put GETDATE in a function.
1) Why not?
2) What can I do to make this work?
MauryHi
Yes , you cannot , however you are be able to pass GETDATE() function as a
parameter onto the UDF and later to operate with it
> I'm trying to make three functions, Today, Yesterday and Tomorrow
SELECT
DATEADD (d,DATEDIFF(D,0,GETDATE()-1),0) AS Yesterday ,
DATEADD (d,DATEDIFF(D,0,GETDATE()),0) AS Today,
DATEADD (d,DATEDIFF(D,0,GETDATE())+1,0) AS Tomorrow
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7B10973B-0A65-4E45-848C-441ADB34E8F0@.microsoft.com...
> I'm trying to make three functions, Today, Yesterday and Tomorrow. But I
> can't, because you can't put GETDATE in a function.
> 1) Why not?
> 2) What can I do to make this work?
> Maury|||"Uri Dimant" wrote:
> Yes , you cannot
Any idea why? I can't seem to think of any good reason for this.
> however you are be able to pass GETDATE() function as a
> parameter onto the UDF and later to operate with it
UDF?
> DATEADD (d,DATEDIFF(D,0,GETDATE()-1),0) AS Yesterday ,
Oh, I know how to do it mechanically, but it's precisely all that syntax
that I'm trying to avoid!
Maury|||Maury Markowitz wrote:
> "Uri Dimant" wrote:
>> Yes , you cannot
> Any idea why? I can't seem to think of any good reason for this.
>> however you are be able to pass GETDATE() function as a
>> parameter onto the UDF and later to operate with it
> UDF?
>> DATEADD (d,DATEDIFF(D,0,GETDATE()-1),0) AS Yesterday ,
> Oh, I know how to do it mechanically, but it's precisely all that syntax
> that I'm trying to avoid!
> Maury
>
Read this, it will explain all:
http://msdn.microsoft.com/msdnmag/issues/03/11/DataPoints/|||When a function is executed, it has to know exactly what data it will
operate on. Getdate() is a 'non-determinate' function since every time it
operates, it produces a different value.
'UDF' = user defined function. A prefix that some use in naming functions.
Also in popular usage is 'fn'.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:15F3B36E-49DD-4539-8B24-1DBE13AA3CC7@.microsoft.com...
> "Uri Dimant" wrote:
>> Yes , you cannot
> Any idea why? I can't seem to think of any good reason for this.
>> however you are be able to pass GETDATE() function as a
>> parameter onto the UDF and later to operate with it
> UDF?
>> DATEADD (d,DATEDIFF(D,0,GETDATE()-1),0) AS Yesterday ,
> Oh, I know how to do it mechanically, but it's precisely all that syntax
> that I'm trying to avoid!
> Maury
>|||"Arnie Rowland" wrote:
> When a function is executed, it has to know exactly what data it will
> operate on. Getdate() is a 'non-determinate' function since every time it
> operates, it produces a different value.
This doesn't really tell me WHY though. WHY does returning a different
datetime make it impossible to use in a function?
Consider this:
ALTER FUNCTION dbo.StartOfDay(@.date datetime) RETURNS datetime
BEGIN
RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
'/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar, DAY(@.date))))
END
This would be called like...
SELECT dbo.StartOfDay(GETDATE())
Now what is it about exactly that precudes this solution:
ALTER FUNCTION dbo.StartOfDay() RETURNS datetime
BEGIN
SELECT @.date = GETDATE()
RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
'/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar, DAY(@.date))))
END
The end result is exactly the same. The code that needs to run is exactly
the same. The actual execution is almost identical. So what is it about the
function creation mechanism inside SQL Server that makes this illegal?
Maury|||In your first example, the function is NOT passed in the function getdate(),
is it passed in the current returned value from getdate() -so therefore, the
function knows exactly the values it has to operate with.
In your second example, the function would not know the value of getdate()
until after it starts working -and that is, by design, not allowed.
Why does 0/1 create such a turmoil in math. I don't know 'exactly, but I
accept the it just isn't allowed.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14D32701-04DF-4A86-B680-6C1669715DEF@.microsoft.com...
> "Arnie Rowland" wrote:
>> When a function is executed, it has to know exactly what data it will
>> operate on. Getdate() is a 'non-determinate' function since every time it
>> operates, it produces a different value.
> This doesn't really tell me WHY though. WHY does returning a different
> datetime make it impossible to use in a function?
> Consider this:
> ALTER FUNCTION dbo.StartOfDay(@.date datetime) RETURNS datetime
> BEGIN
> RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
> '/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar,
> DAY(@.date))))
> END
> This would be called like...
> SELECT dbo.StartOfDay(GETDATE())
> Now what is it about exactly that precudes this solution:
> ALTER FUNCTION dbo.StartOfDay() RETURNS datetime
> BEGIN
> SELECT @.date = GETDATE()
> RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
> '/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar,
> DAY(@.date))))
> END
> The end result is exactly the same. The code that needs to run is exactly
> the same. The actual execution is almost identical. So what is it about
> the
> function creation mechanism inside SQL Server that makes this illegal?
> Maury|||User-defined functions are by definition "deterministic". A deterministic
function is one in which, for every value (or set of values) you pass into
it, it returns the same result. Examples of deterministic functions include
sine(), cosine(), etc.
Because of this limitation on determinism in user-defined functions, you are
not allowed to use non-deterministic functions inside your UDF's (like rand
or getdate). There are ways around this limitation, like creating a VIEW
composed of SELECT GETDATE(); and selecting from the VIEW from within your
UDF.
MS warns against relying on UDF's that circumvent determinism like this and
don't return the same results for the same set of values every time,
however.
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14D32701-04DF-4A86-B680-6C1669715DEF@.microsoft.com...
> "Arnie Rowland" wrote:
>> When a function is executed, it has to know exactly what data it will
>> operate on. Getdate() is a 'non-determinate' function since every time it
>> operates, it produces a different value.
> This doesn't really tell me WHY though. WHY does returning a different
> datetime make it impossible to use in a function?
> Consider this:
> ALTER FUNCTION dbo.StartOfDay(@.date datetime) RETURNS datetime
> BEGIN
> RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
> '/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar,
> DAY(@.date))))
> END
> This would be called like...
> SELECT dbo.StartOfDay(GETDATE())
> Now what is it about exactly that precudes this solution:
> ALTER FUNCTION dbo.StartOfDay() RETURNS datetime
> BEGIN
> SELECT @.date = GETDATE()
> RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
> '/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar,
> DAY(@.date))))
> END
> The end result is exactly the same. The code that needs to run is exactly
> the same. The actual execution is almost identical. So what is it about
> the
> function creation mechanism inside SQL Server that makes this illegal?
> Maury|||"Mike C#" wrote:
> Because of this limitation on determinism in user-defined functions
But why does this limitation exist? Is there a technical reason for this?
Everything I've seen so far says "you can't do it because we don't let you".
> or getdate). There are ways around this limitation, like creating a VIEW
> composed of SELECT GETDATE(); and selecting from the VIEW from within your
> UDF.
Which is baffling. If there is a real, technical, issue here, then this
shouldn't work either.
> MS warns against relying on UDF's that circumvent determinism like this and
> don't return the same results for the same set of values every time,
> however.
Do they give a reason anywhere? I've looked at the Transact-SQL docs, the
document someone posted here, and done a few googles on it too, but every
single case states the fact, and not the reason.
It's all rather maddening.
Maury|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:FA0444D2-F5B4-4CC2-820A-CB654C0455B0@.microsoft.com...
> "Mike C#" wrote:
> > Because of this limitation on determinism in user-defined functions
> But why does this limitation exist? Is there a technical reason for this?
> Everything I've seen so far says "you can't do it because we don't let
you".
One reason I SUSPECT is that in non-deterministic cases, things like
updates/inserts could cause wonkiness and violate ACID.
UPDATE FOO set entry_date=getdate()
All rows updated will have the exact same date/time, regardless of the
number of rows updated.
Now consider something like:
UPDATE BAR set entry_date=fn_mydate()
(assuming fn_mydate() basically is a user defined-function that returns
getdate() for simplicity's sake)
It may be harder for MS to guarantee that the optimizer only returns one
deterministic value for fn_mydate.
(worse case, every row gets a different date/time value.)
Now you've violated ACID.
This is just a guess on my part.
> > or getdate). There are ways around this limitation, like creating a
VIEW
> > composed of SELECT GETDATE(); and selecting from the VIEW from within
your
> > UDF.
> Which is baffling. If there is a real, technical, issue here, then this
> shouldn't work either.
> > MS warns against relying on UDF's that circumvent determinism like this
and
> > don't return the same results for the same set of values every time,
> > however.
> Do they give a reason anywhere? I've looked at the Transact-SQL docs, the
> document someone posted here, and done a few googles on it too, but every
> single case states the fact, and not the reason.
> It's all rather maddening.
> Maury
Monday, March 12, 2012
Get the recent records
i have a datetime field in the post tables.
I would like to get the records within the latest 7 days.
Are there any functions for doing something like this?
my current query is something like
select * from post where creation_time ...
Thank you
try something like this:
create
table #test(datedatetime)insert
into #testvalues
('01/01/2007')insert
into #testvalues
('02/01/2007')insert
into #testvalues
('02/04/2007')
select
*from #testwhere
date>dateadd(day,-7,getdate())drop
table #testI think that it will point you in correct direction, or maybe it is your solution?
|||Could you not just do...
select
*from post
where
creation_time >dateadd(day,-7,getdate())
jpazgier, i am not following the reason for creating the additional table.
|||I just try to provide working example in my answer so I created temporary table with my test data to show that it works and for future testing.
But in this case my example only points your how you can try to solve problem, you maybe would like to take care about not only day but also minutes?
This example if you run it at 12:31 today will show records inserted after 12:31 7 days ago so records inserted at 12:30 will be not visible and maybe author of the post would like to take care about this himself, I do not know if time of the day is important for him or not.
Thanks
|||Both answers are great!|||Both answers are great!
Thank you
Subscribe to:
Posts (Atom)