Getdate() Error In Function

May 2, 2005

I have the below function which errors out telling me "Invalid use of 'getdate' within a function." I can run it as sql but not as a function, is there an issue with using getdate() in a function?

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO




ALTER function fnGetQuantity( @orderid int )
returns int
as
/************************************************** ****************************
** File: fnGetQuantity.sql
** Name: fnGetQuantity
** Desc: Used to calculate the total order quantity for an order
** Quantity is sigfrequency.timesperday * sigdosageamt.descr * orders.duration
**
** Return values: Quantity
**
** Called by:
**
** Parameters: order Id
** Input Output
** ---------- -----------
** @patientid
** Auth: DHoefgen
** Date: 04/30/05
************************************************** *****************************
** Change History
************************************************** *****************************
** Date: Author: Description:
** -------- -------- ------------------------------------------
** 05/02/05 KKowert Changed sql for effdt and added duration and
** times per day logic for zeros.
************************************************** *****************************/
begin
declare @QuantityTotal int


SELECT @QuantityTotal = (o.Duration * f.TimesPerDay * d.Descr)
FROM Orders o INNER JOIN
SIGFrequency f ON o.FreqID = f.FreqID INNER JOIN
SIGDosageAmt d ON o.DosageAmtID = d.DosageAmtID
WHERE (o.OrderID = @orderid) AND (f.Effdt =
(SELECT MAX(f2.Effdt)
FROM SIGFrequency f2
WHERE f2.FreqID = f.FreqID AND f2.Effdt <= getdate())) AND (d.Effdt =
(SELECT MAX(d2.Effdt)
FROM SIGDosageAmt d2
WHERE d2.DosageAmtID = d.DosageAmtID AND d2.Effdt <= getdate()))


return @QuantityTotal

end




GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

View 2 Replies


ADVERTISEMENT

Getdate() Within A Function

Mar 6, 2006

Im really new to SQL SProcs.  I have a function that I wrote that I am trying to compare a date within a record to today's date.  The problem is that you cant call getdate from within a function...  So, I was thinking that I could create a temp table that had a a date column with a default date of today and select that out.  However, I cant find any documentation on how you would create a temp table with a default value, or if this would even work.  I dont want to have to pass todays date into the function, nor do I want to have to create a permanent table just to hold this data.
Any help, or other ideas?
Thanks.

View 4 Replies View Related

Getdate() Function

Nov 16, 2000

The getdate () function returns the following
eg: 2000-11-16 16:13:49.717

Do anyone knows of a function to return only the date eg: 2000-11-16???
Not the time.

Thanks!

View 1 Replies View Related

Bad Function GetDate()...

Feb 19, 2001

Does anybody knows if there is a problem que SQL Server 7.0 in the date?

I Created this Trigger...

CREATE TRIGGER pone_fecha_actual
ON Pendent_Invoices
FOR INSERT
AS UPDATE Pendent_Invoices
SET Publication_Date = convert(char(10),getdate(),7)
WHERE Publication_Date IS NULL

But when browse the information it appears thie date... 1900-01-01 00:00:00.000

Thanks!

View 2 Replies View Related

Using The GetDate Function

Jan 4, 2005

Hi
Having some trouble using the getdate function here is some sample code that i am using for an ASP application:

SELECT COUNT(spotnum) AS total FROM spotcheck WHERE year(checkdate) = year(GETDATE())

that will give me a count of all records for this year, how do i get a count for records last year? year(GETDATE(-1))????

sorry im so clueless

-Jesse

View 2 Replies View Related

Cannot Use GETDATE ( ) In A Function?

Dec 22, 2005

I want to use GETDATE ( ) in a user defined function for date comparing. When I compile the function, got the error:

Invalid use of 'getdate' within a function.

How to get the current date in a function? Any suggestion will be appreciated.

ZYT

View 2 Replies View Related

About Function:GETDATE()

Apr 25, 2006

Hello,
I useSELECT CONVERT(VARCHAR(30),GETDATE(),105)
and get the now data
25-04-2006,
but if i want to get the data format is 2006-04-25
How?
Thank you!

View 4 Replies View Related

Getdate Function SQL

Apr 11, 2007

I am trying to return data from between 2 dates. in this instance it is returning data from yesterday which works fine.
However I am trying to return data for yesterday before 12 noon.
The following returns yesterdays data.
WHERE
(dbo.People.SystemUser = 1 AND
dbo.Actions.ActionDate > DATEADD(dd, DATEDIFF(dd,0,getdate()), -1) AND
dbo.Actions.ActionDate < DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) AND
dbo.People.PersonId = 'JO')

How would I get it to return only yesterday before midday's data?

Thanks,
Tugsy

View 2 Replies View Related

Problem With GETDATE() Function.

Mar 7, 2006

Hi everyone,

DECLARE @altstartdate datetime
SET @altstartdate = getdate()
set @altstartdate = @altstartdate -1
select * from tbl_document where convert(char,dicreationdttm,101) = convert(char,@altstartdate,101)

The person who wrote the code was trying to get the previous day of data, So today is 03/07/06, within the database it only have data from 03/06/06 and back 15 days, so what I want is data from 03/06 - -2/20, which I believe is 15 days worth of data, How would I go about geting 15 days of data, do I still need to use getdate() function. Any help please. Thanks

Lystra

View 2 Replies View Related

Data By ID And Getdate Function

Apr 13, 2007

I am trying to return data by date but I also need to return data by date "and" by ID. I've queried various select statements but always get only one days worth of data and the date is repeated many times. I only want to return data for today only but with ID as the other variable.

For example I want only the shows for today by date and by ID. ID of course being the key in the DB. Below I will show you a code block followed by a text version of what it looks like in the browser when tested.



Code Snippet


<%
set con = Server.CreateObject("ADODB.Connection")
con.Open "File Name=E:webserviceKuowKuow.UDL"
set recProgram = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT *, Air_Date AS Expr1 FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101))"

'strSQL = "SELECT *, Air_Date AS Expr1, Unit AS Expr2 FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101)) AND (Unit = 'TB')"
recProgram.Open strSQL,con
%>

<%
recProgram.Close
con.Close
set recProgram = nothing
set con = nothing
%>

Output:
ID Unit Subject Title Long_Summary Body_Text Related_Events Air_Date AudioLink

(Reading across the screen from left to right)
1234 WK1 Subject Title a summary some body text Event text 4/13/2007 wkdy20070413-a.rm

Here is the URL used for testing:
http://Test Server IP/test/defaultweekday2.asp

I need to be able to append to this URL an ID number so that not only do I get content by Air_Date but also by ID.

http://Test Server IP/test/defaultweekday2.asp?ID=1234

How to do this?

View 10 Replies View Related

SQL SERVER Invalid Use Of 'getdate' Within A Function.

Feb 5, 2004

alter function sp_insert_newCustomer
(
@CrmDbIDint,
@DealerIDint
)
returns int
--@AddressLastUpdatedatetime
as
begin

declare @date datetime
set @date = getdate()

insert into dbo.CRMDB values(
@DealerID,
@date --@CreatedDate,
)

return @@Identity
end
GO

View 3 Replies View Related

How To Get Current Date Using Getdate() Or Any Other Function

Aug 26, 2002

Hi!

I'm new to T-SQL and trying out my queries using the SQL Query Analyzer.

What I intend to do is to retrieve records based on current date. The field is Trans_R.Tr_Date, of type nvarchar(255) and the data inside the field is formatted as yyyymmdd.

My Q's are:

1. Where should I put the WHERE statement at?

2. Is there any other way to work around this problem without resorting to converting the Trans_R.Tr_Date field to Datetime datatype beforehand?

3. I tried using Getdate function as part of the WHERE statement but nothing comes out. Would this WHERE statement work (or make sense)?

WHERE Trans_R.Tr_date = getdate()

Please advise and thanks in advance.

-------------
START OF CODE
-------------

SELECT
Cards.card_number,Trans_R.Tr_Date,Trans_R.staff_nu mber,
MIN(Trans_R.Tr_In) AS Actual_Time_In,
MAX(Trans_R.Tr_Out) AS Actual_Time_Out,

'Status'= CASE
WHEN MIN(Trans_R.Tr_In) IS NULL THEN 'ABSENT'
WHEN MIN(Trans_R.Tr_In) < 171500 THEN 'ATTEND'
ELSE 'ABSENT' END,

'Late Time' = CASE
WHEN (MIN(Tr_In) - 080000> 0) THEN (MIN(Tr_In) - 080000)
ELSE '' END,

FROM Trans_R INNER JOIN Cards
ON Trans_R.Staff_Number = Cards.Staff_Number
GROUP BY Cards.card_number,trans_r.staff_Number,Trans_R.Tr_ Date

-------------
END OF CODE
-------------

View 2 Replies View Related

How To Subtract 1 Hour From GetDate() Function

Sep 15, 1999

Can any one tell me how to subtracts and add hours to the current date and time?
In my case I have to store the Vancouver date and time in Toronto.
Thanks,
T.Lingam

View 1 Replies View Related

GETDATE() With A User Defined Function

Jun 9, 2004

Hi,

I have a requirement where i need to get the current time/date within a Function. As getDate function is a non deterministic function it can not be used with in a function. Your guidence in this regard is greately appreciated.

Regards,
Samcute.

View 11 Replies View Related

Getdate() In User Defined Function

Aug 13, 2004

Hi,

It is possible to use getdate() in userdefined function. If so, how to do the same ?

The following code throws error :

create function function1
return varchar
DECLARE @currYYMM VARCHAR(20)
SET @currYYMM = convert(char(4),getdate(),12)
// Here it says the error 'getdate' can't be used inside functions
...............
.....................

View 2 Replies View Related

Column Heading Getdate Function

Jan 7, 2015

I would like to make the column heading to be the current year for the Sales I'm adding below.

SELECT dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no,dbo.arslmfil_SQL.slspsn_name,
SUM(CASE WHEN year(getdate()) = qivsalesmtdytdcustsalesperson.year THEN Sales END) AS convert(varchar(4),year(getdate()))
FROM dbo.QIVSalesMTDYTDCustSalesPerson INNER JOIN
dbo.arslmfil_SQL ON dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no = dbo.arslmfil_SQL.humres_id
GROUP BY dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no, dbo.arslmfil_SQL.slspsn_name

What I have now gives me incorrect syntax near keyword convert.

View 2 Replies View Related

MS SQL GetDate() Function Remove Time

Jul 4, 2007

Hi,I am creating creating a table with a Date column dd-mm-yyyy. But Icant seem to find a SQL function that just returns today's date.getDate() returns the time as well so I cant use it.The reason is simply that I want to update/overwrite over and overagain all records from current day but not touch the ones fromyesterday etc and with the timestamp in there I just end up addingmore and more rows for the same day.In other words I only want to preserve rows are from yesterday orolder but overwrite ones from today.Any help will be appricated.Thank you!Yas

View 3 Replies View Related

Sql Server 2005 GETDATE() Function

Apr 22, 2008

I have a small problem using the GETDATE() function from a JAVA program using the MS JDBC connector.

When I use the SQL Mangment Studio Express to execute this query:

SELECT GETDATE()
it returns 2008-04-22 16:25:03.690 which is OK !!

I want to get that same value using a Java program so I do that query but when I display it it shows as:

2008-04-22 and there is no time there !
I tried formatting the date to include time but it shows as: 04/22/2008 12:00:00 AM
So maybe the time isn´t there ?

This is the code i am using:

Statement st = con.createStatement();
String sql = "SELECT GETDATE()";
ResultSet result1 = st.executeQuery(sql);

result1.next();

Date today = result1.getDate(1);


Any help would be appreciated.

View 4 Replies View Related

Problem With Getdate Function In Optional Parameters

Mar 29, 2007

Hi, I want to write a StoredProcedure with one optional input parameter of Date and when it is missing I want current date to be used.
I have written the following StoredProcedure, but getdate function doesn`t work. If I replace it with a constant date, it works.
ALTER PROCEDURE [dbo].[LinksFees_Record]
@Date datetime = getdate
AS
INSERT INTO LinkSearchFees
(LinkID, Price, [Date])
SELECT ID AS LinkID, SearchDayFee AS Price, @Date
FROM Links
WHERE (SearchDayFee > 0)
 
RETURN
When I call the StoredProcedure the following exception occur: Conversion failed when converting datetime from character string.
How can I fix it? 
 
 
 

View 1 Replies View Related

Transact SQL :: GetDate Function Not Returning Any Results?

May 27, 2015

I am having a problem with the GETDATE().

WHERE TableName.ColumnNamne = Getdate()

The above SQL function does not return any results whereas the below SQL code returns results. Am I doing anything wrong?

WHERE SalesOrder.New_ActualShipmentDate >= GETDATE()

View 35 Replies View Related

Transact SQL :: Get 2 Different Result Sets With GetDate Function

Aug 4, 2015

Periodically throughout the day a report is manually pulled from a SQL Server database.  Is their a way w/o me adding a field to the database to have the result set return the "new" results?  For example, lets say this is our DDL

Create Table OneTwoThree
(
id int
,date11 datetime
,firefly varchar(10)
)

Insert Into OneTwoThree Values (1, '08/03/2015 18:43:32.012', 'Hi'), (2, '08/03/2015 18:44:11.011', 'No'),
(3, '08/03/2015 19:36:33.011', 'Second'), (4, '08/03/2015 19:37:33.011', 'Alpha')

Prior I could use this syntax, but that was only with needing to generate 1 result set.  

Select id, convert(varchar(10), date11, 101) As [Date], firefly from onetwothree
where CONVERT(varchar(10), date11, 101) < CONVERT(varchar(10), GetDate(), 101)

Looking at my datetime values, let's say the 1st was generated at 18:45, obviously the 1st two records in the table would be returned. And let's say a 2nd time I need to generate I want to exclude the 1st two entries as they have already been verified. How can I do such w/o adding a field to the table?

View 11 Replies View Related

[ask] Sqlserver Getdate() Function Doesn't Return Seconds Value

May 24, 2007

 i have a quite strange condition...when i add some value in database with getdate() function it only returns date and minute not the seconds...does somebody have an experience about this 

View 3 Replies View Related

Derived Column Returning No Data On GetDate() Function

Aug 1, 2006

Hi all--I've got a derived column transformation where I am adding a field called Import_Date. I'm telling it to add as a new column and use the function "GetDate()" to populate the field. When I run the package, it returns NULL as the data value for all rows. Any idea why this might be happening?

View 5 Replies View Related

Automated Testing | How To Force GETDATE() Function To Return Specific Value?

Jul 20, 2005

Hello,Our QA team have running a lot of test scripts (for automated regressiontesting), they run them on the different databases (Oracle/MS SQL).Several of those tests are dependent on the current date/time. In order tobeable to use them efficiently, we changed the current date/time on the QAdatabase server to a specific date/time before starting the scripts, so weare sure the test scripts always run in the same environment.Resetting the date/time of the database server gives us more and moreproblems (OS problems, backup/ virusscan, ...).It is possible to fix the problem with SYSDATE function on Oracle by settingFIXED_DATE init parameter.Is it possible to 'change' the current date/time on 'database' level,instead of on OS level for MSSQL2000?Do you know other means to do such things?Thanks in advance,Konstantin

View 2 Replies View Related

Use Getdate Function In A Own Function

Jul 20, 2005

Hi,I have written a stored proc with some temporary tables and also useda getdate() in my stored proc. When i try to call the sproc the erroris that we can only use extended sprocs or function inside a sproc.Now if try to write the stored proc directly inside a fuction ie copypaste after changing my temp tables to tables the problem is , i get aerror invalid use of getdate in sproc.What do i do to get somethingfor my results inside a table.Thanks in advance.RVG

View 5 Replies View Related

No Authorized Routine Named GETDATE Of Type Function Having Compatible Arguments Was Found. Sqlstate:42884

May 30, 2008



Hi All,

I am inserting a row in my SSIS_LOG table when my package executes. Here in this table i have column date_dt which is date data type. Once package executes i am getting error like "No authorized routine named "GETDATE" of type "Function" having compatible arguments was found. sqlstate:42884". Note i am using IBMDB2 data provider.

Can anyone help me out?

Thanks in advance,
Anand Rajagopal

View 9 Replies View Related

GetDate() Throws Error With MS JDBC Driver V1.2

Oct 24, 2007

Hi,

I have a small program to query a stored procedure in SQL Server 2000 and print out the contents of a returned date field. The program is as follows:

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbcqlserver://host:1433;database=db;user=u1;password=p1";
Connection con = DriverManager.getConnection(connectionUrl);
CallableStatement cbstmt = con.prepareCall(" {call stored_proc (?,?)}");

cbstmt.setString(1, "value1");
cbstmt.setString(2, "value2");
ResultSet resultSet = cbstmt.executeQuery();

while (resultSet.next()) {
System.out.println(resultSet.getDate("end_date"));
}

The date field is nullable, and the records in the database are currently null. But when I run this program, I get the following exception when using version 1.2 of the MS JDBC Driver:

com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from int to DATE is unsupported.
at com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.DTV.getValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.Column.getValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.Column.getValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getDate(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getDate(Unknown Source)
at jdbc.main(jdbc.java:38)
Exception in thread "main"

This started happening when we upgraded to the new driver. Any ideas why?

Thanks.

View 1 Replies View Related

Getdate() >= Startdate And Getdate() <= Enddate

Oct 4, 2000

Please i need an exmple of ur solution, thanks :)

I'm using some files to show certain pages on certain date for an example

File name : aa.doc
start date: 10/02/00
end date : 10/03/00

But it expires on 10/02/00, here is the strored procedure:

Before the date comes, it expires the page
Here is my stored procedure:

"
SELECT startdate, enddate,archivedate
and (startdate is null or (getdate() >= startdate and getdate() <= enddate))
and (archivedate is null or (getdate() <= archivedate))
group by startdate, enddate order by startdate desc "

Thankx a lot

View 1 Replies View Related

Getdate() In Derived Columns Shows Error Outputcolumnlineageid

Feb 15, 2008

Hi,

In my working existing package I am adding a derived column as date and datatype as DT_DBTIMESTAMP and my destination data type is datetime. when I try to union these records through an "Union ALL" component its not allowing me to map this column and throws error as "outputcolumnlineageid " and defines the error as metadata for source and destination are not matching.
I checked the metadata and they are DT_DBTIMESTAMP, Am I missing something? please advice.

View 4 Replies View Related

Select Convert(varchar(16), Getdate(), 101)+LEFT(REPLACE(convert(varchar, Getdate(), 108), ':', ''),4)

Sep 26, 2007



select convert(varchar(16), getdate(), 101)+LEFT(REPLACE(convert(varchar, getdate(), 108), ':', ''),4)

From above query I get

mmddyyyyhhmm

but it' s yyyy and hour can not be separated

04/12/200702:05

How can I separated the year and hour ?

Thanks
Daniel

View 2 Replies View Related

Error While Creating Inline Function - CREATE FUNCTION Failed Because A Column Name Is Not Specified For Column 1.

Apr 3, 2007



Hi,



I am trying to create a inline function which is listed below.



USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO



while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.



Pleae help me to fix this error



thanks

Purnima

View 3 Replies View Related

Function Error

May 21, 2008

what is the error in that function, please help to find out

create function [dbo].[ufn_IsLeapYear] ( @pDate DATETIME) returns bit
as
begin
declare @b bit


if (YEAR(@pDate)%4=0 ) AND YEAR(@pDate)%100 != 0))
set @b = 1
else if (YEAR(@pDate) % 400 = 0)
set @b= 0
Return(@b)
end


spatle

View 1 Replies View Related

Error With Max Function

Jun 8, 2014

I have the following SQL:

SELECT
MAX(Case WHEN A2.AttrID = '23' AND A2.DefID = '9719132' THEN (Select Name from kua where ID=A2.Valint) END) as Manage
FROM ree A1 inner join data A2 on A1.DataID=A2.ID
AND
A1.SubType=31066
inner join ancestors AN ON AN.DataID = A1.DataID and AN.AncestorID=9735190

Every time I run it i get this error:Cannot perform an aggregate function on an expression containing an aggregate or a subquery...If I change the Field definition to:

MAX(Case WHEN A2.AttrID = '23' AND A2.DefID = '9719132' THEN (A2.Valint) END) as Manager

then it works. The query Select Name from kua where ID=A2.Valint has only one field as result. Why do I get an error?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved