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 regression
testing), they run them on the different databases (Oracle/MS SQL).
Several of those tests are dependent on the current date/time. In order to
be
able to use them efficiently, we changed the current date/time on the QA
database server to a specific date/time before starting the scripts, so we
are sure the test scripts always run in the same environment.
Resetting the date/time of the database server gives us more and more
problems (OS problems, backup/ virusscan, ...).
It is possible to fix the problem with SYSDATE function on Oracle by setting
FIXED_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?
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
I am looking for a number of concept hierarchies (i.e. hierarchy with is-a relationships) describing the same domain that are accompanied with the instances from which the concept hierarchy was obtained. I have been trying to find some data suitable for representing the extracted knowledge in form of concept hierarchies so that can obtain at least two concept hierarchies from the same domain that differ slightly. In this sense the data needs to have multiple class (target) values and there has to be a hierarchical relationship between the classes. I have looked at the datasets publically available from 'uci' website and some others, but so far it appears that only the Zoo dataset is suitable to be represented in a form of concept hierarchy but it is still closer to a decision tree. I have seen some bird-domain or more specifc animal kingdom hierarchies but I cannot find those more specific datasets. Does anyone know where some concept hierarchies with instances can be obtained or datasets with multiple hierarchically related class values? Any help would be greatly appreciated.
I want the following query to return a row even when table 'X' is empty. How would I do this? SELECT TOP 1 @Var1, @Var2, @Var3 from X The parameters @Var1, @Var2 and @Var3 are passed to the stored procedure in which the above query is included. When table is empty, it reurn nothing. It only return a row when table is not empty.
Hi all! I've been banging my head on this for way to long and decided it's time to reach out for help. I'll try to be complete and concise with this, but this will end up being lengthy most likely. The crux of the issue is that I apparently do not have any control over what is returned from a stored proc via the SqlCommand.ExecuteNonQuery method. I want the procedure to explicitly return 0 if the proc was successful, otherwise it will return the error code generated. A value of -1 is consistently being returned when SET NOCOUNT ON is in place, and when not, a 1 is returned. This seems to be ignoring the fact that I have RETURN 0 at the end of the proc. Perhaps I'm missing something, but if I RETURN 0, I should get 0...here is the procedure:1 ALTER PROCEDURE [dbo].[epsp_EditCities] 2 @CityId int = null, 3 @City varchar(50), 4 @CreatedBy int, 5 @UpdatedBy int, 6 @ActionType varchar(1) = 'X', 7 @Identity int = 0 OUT, 8 @RowCount int = 0 OUT 9 10 AS 11 DECLARE @ERROR int -- Local @@ERROR 12 13 14 SET NOCOUNT ON 15 BEGIN TRAN 16 17 IF @ActionType = 'I' /* --- INSERT --- */ 18 BEGIN 19 20 INSERT INTO [EnrollmentPrograms].[dbo].[Cities] 21 ([City] 22 ,[CreatedBy] 23 ,[UpdatedBy]) 24 VALUES 25 (@City 26 ,@CreatedBy 27 ,@UpdatedBy) 28 SET @Identity = SCOPE_IDENTITY() 29 30 END 31 32 IF @ActionType = 'U' /* --- UPDATE --- */ 33 BEGIN 34 35 UPDATE [EnrollmentPrograms].[dbo].[Cities] 36 SET [City] = @City 37 ,[UpdatedBy] = @UpdatedBy 38 WHERE CityId = @CityId 39 END 40 41 IF @ActionType = 'D' /* --- DELETE --- */ 42 BEGIN 43 44 DELETE FROM [EnrollmentPrograms].[dbo].[Cities] 45 WHERE CityId = @CityId 46 END 47 48 49 -- Error checking (place this after every statement) -- 50 SET @ERROR = @@ERROR 51 SET @RowCount = @@ROWCOUNT 52 53 IF @ERROR != 0 GOTO HANDLE_ERROR 54 55 COMMIT TRAN -- No Errors, so go ahead 56 57 RETURN 0 58 59 HANDLE_ERROR: 60 ROLLBACK TRAN 61 RETURN @ERRORAnd a fascinating tidbit is the results from this which shows the return value as 0:1 DECLARE@return_value int, 2 @Identity int--, 3 --@RowCount int 4 5 EXEC@return_value = [dbo].[epsp_EditCities] 6 @CityId = 1, 7 @City = N'Agoura Hills', 8 @CreatedBy = 1, 9 @UpdatedBy = 1, 10 @ActionType = N'U', 11 @Identity = @Identity OUTPUT--, 12 --@RowCount = @RowCount OUTPUT 13 14 SELECT@Identity as N'@Identity'--, 15 --@RowCount as N'@RowCount' 16 17 SELECTReturnValue = @return_value
And finally, here is the C# call that always returns -1: retVal = cmd.ExecuteNonQuery(); Now, I've tried almost all variations and unit tests on the procedure that I could dream up. Commenting this out and putting this in etc....I still remain unable to fix the return code to 0 and get that result into my retVal var. Any love? Much thanks in advance for spending time on my issue! Cheers! Wayne
Hi All,I need help in creating a function in VB for my ASP.NET application where I want to add records to database on the first day of every month.I have got no idea about what I have to do for achieving this goal.Its basically for a customer based application where Interest will be paid into customers' account and I need to implement this for every customer on 1st day of every monthThe thing I am not sure about is how can I get the application to add a record for each customer on the first day of each month, i.e. how can I get the application to check that its 1st day of month and then the application adds records automatically for each customer based on my specified rules.If any of you could help me with this, I'll really appreciate it.Thank you.
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.
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
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!
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?
Hi, I need to return the system date to a column when a checkbox in another column becomes true, e.g. the instant a user updates a table where checkbox.value = 'true' it will record the date that it happened in another column.
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
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
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
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
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
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.
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 ............... .....................
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.
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
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 ?
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?
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) )
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?
I'm trying to create a report that's based on a SQL-2005 Stored Procedure.
I added the Report Designer, a Report dataset ( based on a shared datasource).
When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.
I'll only reproduce one instance of the error for the sake of brevity.
[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.
I've searched on this error and it looks like it's a Visual Basic error :
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?
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
i am trying to make a highscore table and i would like when the user enters a score for them to be able to see what position they are in the database without having to take out all the data. i have looked at a few methods and was wondering which one is the best solution and how i would go about implementing it
