Stored Procedure Giving Error When Searching On Date Range

Aug 20, 2007

I've  a report whose columns are returned from a stored procedure. Now I want to display the report based on a date range. The date field is Received. It's in dbo.master. I added 2 parameters start date and end date. When I check the condition if dbo.master.Received>StartDate and dbo.master.Received < EndDate directly I'm getting error. Could someone tell me what mistake I'm doing? Thanks for your help!ALTER Procedure [dbo].[USP_Reports_NewTier1]

 

@ClientCode VARCHAR(7) = '',@UserID INT = 0

,@OrderID INT =0

,@StartDate datetime,@EndDate datetime

 

IF @ClientCode <> '' and dbo.master.Received > StartDate and dbo.master.Received<EndDate
BEGIN
 SELECT --Root Select
 --ClientName
 @ClientName = (Select Name  FROM dbo.customer c
    WHERE   c.Customer = @ClientCode)  
  
 ,@TotalDollarValue = (SELECT SUM(m.current1-m.paid1)
    FROM dbo.master m
    WHERE phase=1
    AND m.Customer = @ClientCode
    AND M.Status <> 'PIE')


 ,@AverageAge = ISNULL((select avg(age) from
   (select datediff(day,Received,CASE WHEN clidlp>clidlc then clidlp else clidlc END)* -1 as age
   from dbo. master M
   WHERE phase=1 AND customer = @ClientCode AND M.Status <> 'PIE') x),0)
END

View 3 Replies


ADVERTISEMENT

Query Help - Giving A Date Range Given The Start Date, Thanks!

Jul 23, 2005

Hi Group!I am struggling with a problem of giving a date range given the startdate.Here is my example, I would need to get all the accounts opened betweeneach month end and the first 5 days of the next month. For example, inthe table created below, I would need accounts opened between'5/31/2005' and '6/05/2005'. And my query is not working. Can anyonehelp me out? Thanks a lot!create table a(person_id int,account int,open_date smalldatetime)insert into a values(1,100001,'5/31/2005')insert into a values(1,200001,'5/31/2005')insert into a values(2,100002,'6/02/2005')insert into a values(3,100003,'6/02/2005')insert into a values(4,100004,'4/30/2004')insert into a values(4,200002,'4/30/2004')--my query--Select *[color=blue]>From a[/color]Where open_date between '5/31/2005' and ('5/31/2005'+5)

View 2 Replies View Related

Reporting Services :: Searching By Single Date Or Date Range

Apr 22, 2015

I would like to be able to search by a single date, @StartDate, or by a date range , between @StartDate and @EndDate. I am having a hard time with the logic on this for a report in SSRS.

View 5 Replies View Related

Searching Date Range

Feb 23, 2007

i have a date field with which i'd like to search several date ranges. the date field is populated as month, day, year (2/23/2007).

I'd like to select date using the following criteria:

< 30 days (i.e. select * from date where today's date < 30)

30 - 60 days (i.e. select * from date where today's date >= 30 and <= 60)

> 90 days (i.e. select * from date where today's date < 90)

Is there a simple way of acheiving this?

Thanks!

View 1 Replies View Related

Stored Procedure To Check Date Range

Nov 10, 2006

Hi guys,I have written a stored procedure to check for date range, say if the user enters a value for 'city-from' , 'city-to', 'start-date' and end-date, this stored procedure should verify these 2 dates against the dates stored in the database. If these 2 dates had already existed for the cities that they input, the stored procedure should return 1 for the PIsExists parameter. Below's how I constructed the queries:  1 ALTER PROCEDURE dbo.DateCheck
2 @PID INTEGER = -1 OUTPUT,
3 @PCityFrom Char(3) = '',
4 @PCityTo Char(3) = '',
5 @PDateFrom DATETIME = '31 Dec 9999',
6 @PDateTo DATETIME = '31 Dec 9999',
7 @PIsExists BIT = 1 OUTPUT
8 AS
9
10 CREATE TABLE #TmpControlRequst
11 (
12 IDINTEGER,
13 IsExistsCHAR(1)
14 )
15 /*###Pseudo
16 1. Check the Date From and Date To
17 -- select all the value equal to parameter cityFrom and cityTo
18 -- insert the selection records into tmp table
19 --*/
20 INSERT INTO #TmpControlRequst
21 (ID, IsExists)
22 SELECT ID,
23 IsExists = CASE WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
24 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
25 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
26 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
27 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
28 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
29 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
30 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
31 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
32 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
33 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
34 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
35 ELSE 0 END
36 FROM RequestTable
37 WHERE ID <> @PID
38 AND CityFrom = @PCityFrom
39 AND CityTo = @PCityTo
40
41 --======== FINAL RESULT
42 -- For tmp table:-
43 -- isExists = 1 ==> date lapse
44 -- isExists = 0 ==> date ok
45 -- if count for (isExists = 1) in tmp table is > 0 then return 1 and data not allow for posting
46 SELECT @PIsExists = CASE WHEN COUNT(*) > 0 THEN 1
47 ELSE 0 END
48 FROM #TmpControlRequst
49 WHEREIsExists = 1
50
51 SELECT @PIsExists
52 --=========
53
54 DROP TABLE #TmpControlRequst
55
56 --=========
57 RETURN(0)However, when I run this stored procedure, 'PIsExists' would always return -1. I am positive that the values that I passed in, had already existed in the database. Any idea what might be causing this problem? Thanks in advance

View 6 Replies View Related

SQLS7&&VB6 Date Update Gives Syntax (Not Date Format) Error In Stored Procedure

Jul 20, 2005

Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated

View 5 Replies View Related

Alter Table Datatype Giving Error Date To Bit

Nov 20, 2014

IF EXISTS (SELECT * FROM SYS.COLUMNS WHERE NAME =N'Case_Escalated__c' AND OBJECT_ID = OBJECT_ID(N'[Case]'))
begin
alter table [Case] alter column Case_Escalated__c bit null
print 'Case_Escalated__c altered sucessfully for [Case]'
end

Msg 1792, Level 16, State 1, Line 3
Alter table 'Case' failed because the added fixed column might cause existing data to go beyond the maximum allowable table row size of 8060 bytes.

View 4 Replies View Related

Calling CLR Stored Procedure From Within A CLR Table-valued Function Giving Errors

Apr 6, 2007

We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table.






Code Snippet

[SqlFunction ( FillRowMethodName = "FillRow",

TableDefinition = "CustomerID nvarchar(MAX)",

SystemDataAccess = SystemDataAccessKind.Read,

DataAccess = DataAccessKind.Read,

IsDeterministic=false)]

public static IEnumerable GetWishlist () {

using (SqlConnection conn = new SqlConnection ( "Context Connection=true" )) {

List<string> myList = new List<string> ();

conn.Open ();

SqlCommand command = conn.CreateCommand ();

command.CommandText = "GetObject";

command.Parameters.AddWithValue ( "@map", "Item" );

command.CommandType = System.Data.CommandType.StoredProcedure;

using ( SqlDataReader reader = command.ExecuteReader ( System.Data.CommandBehavior.SingleRow )) {

if (reader.Read ()) {

myList.Add ( reader[0] as string );

}

}



return (IEnumerable)myList;

}

}



When command.ExecuteReader is called, I am getting an "Object not defined" error. However, the stored procedure can be used in SQL Management Studio just fine.






Code SnippetEXEC GetObject 'Item'



Is there some sorf of trick I am missing?



Thank you!

View 3 Replies View Related

SQL Server 2008 :: Query To Select Date Range From Two Tables With Same Date Range

Apr 6, 2015

I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below

Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000

The table B stores the item process time. The structure is as below

Item ProcessStartDate ProcessEndDate
V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000
B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000
C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000

I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.

Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.

The result expected is

Table A

Name StartDate EndDate
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000

Table B

Item ProcessStartDate ProcessEndDate
A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000
B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000
C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000

Scenario 2 expected result

Table A

Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000

Table B

Item ProcessStartDate ProcessEndDate
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000

View 8 Replies View Related

Stored Proc Date Range Nor Producing RS

Oct 11, 2004

I am going crazy. I have a typical date field in tbl_Feedback(smallDateTime) that is storing just the date portion. I have a stored proc that I want to dynamically retrieve records within a certain date range (typical!). I am using ASP/ADO, etc. When I run the following Stored Proc in SQL Analyzer, it works (if I replace the variable "@sDate" with '10/01/2004' --There ARE records within this date range.) Further more, If I run a simple Query on the Feedback table and do the same, it produces results, but when I call the stored Proc from the web page, I get 0 results when attempting to PASS date values dynamically. I tried to pass them as actual date types, still 0. I'm now attempting to pass it as a 10 length string ("10/01/2004") and converting it to a date, still 0. What am I MISSING???

-- Procedure

CREATE PROCEDURE sp_009_SiteLead
@sDatenVarChar(10)

AS

--DECLARE @CurDate datetime
DECLARE @StartMonth int
DECLARE @StartYear int
DECLARE @StartDay int
DECLARE @StartDate varchar(10)


SET @StartMonth = DATEPART( month, Convert ( datetime, @sDate ))
SET @StartYear = DATEPART( year, Convert ( datetime, @sDate ))
SET @StartDay = DATEPART( day, Convert ( datetime, @sDate ))
SET @startDate = CONVERT( varchar(2), @StartMonth ) + "/" + CONVERT( varchar(2), @StartDay ) + "/" + CONVERT( varchar(4), @StartYear )
Print @startDate

SELECT *
FROMtbl_Feedback
WHERE tbl_Feedback.DateIn >= @startDate
GO

--- ADO Web page

' Stored Procedure Name: sp_009_SiteLead
' Site Lead Default Query

set cmd=server.CreateObject("ADODB.command")
set psDate = cmd.CreateParameter("sDate", adDBTimeStamp, adParamInput)
set cmd.ActiveConnection=DBConnection
cmd.CommandText="sp_009_SiteLead"
cmd.Parameters.Append peDate
cmd.Parameters.Append psdate
psDate.Value = "10/01/2004"
Set rsObj = CreateObject("ADODB.Recordset")
rsObj.ActiveConnection = DBConnection
rsObj.CursorLocation = adUseClient
rsObj.PageSize= 20
rsObj.Open cmd

Again, If I replace the @startDate variable in the stored proc with a date surrounded in single quotes, I get the expected results, but it just does not understand my date if I pass it.
Can anyone help?

Thanks,

Chad

View 5 Replies View Related

.NET Framework :: Searching With Multiple Condition Stored Procedure

Jun 11, 2015

 I want to write a Stored Procedure which can search based on multiple condition and in default is select all.

Example :

User Name [          ALL               ] Textbox
Project Name  [               ALL        ^]  dropdown
ID [      ALL       ^] dropdown
-----------------------------

Display On Gridview(Default display all records)

select one condition search one and so on.

View 5 Replies View Related

Stored Procedure Range Not Working

Dec 22, 2006

Hi All,I am trying to write a basic stored procedure to return a range ofvalues but admit that I am stumped. The procedure syntax used is:ALTER PROCEDURE Rpt_LegacyPurchaseOrderSp(@StartPoNumber PONumberType = 'Null',@FinishPoNumber PONumberType = 'Null')ASSET @StartPoNumber = 'PO_NUMBER_POMSTR'SET @FinishPoNumber = 'PO_NUMBER_POMSTR'SELECTIPPOMST_SID,--Start tbl_IPPOMSTPO_NUMBER_POMSTR,VENDOR_NUMBER_POMSTR,SHIP_NUMBER_POMSTR,CHANGE_ORDER_POMSTR,FOB_POINT_POMSTR,ROUTING_POMSTR,DATE_ISSUED_POMSTR,DATE_LAST_RECPT_POMSTR,CONTACT_PERSON_1_POMSTR,PREPAID_COLLECT_POMSTR,TERMS_POMSTR,AMOUNT_ESTIMATED_POMSTR,AMOUNT_RECEIVED_POMSTR,AMOUNT_PAID_POMSTR,LOCATION_CODE_POMSTR,SHIPPING_POINT_POMSTR,PRINT_IND_POMSTR,BUYER_POMSTR,SHIPMENT_POMSTR,STATUS_POMSTR,CURRENCY_POMSTR,CURRENCY_STATUS_POMSTR,AMOUNT_EST_CUR_POMSTR,AMOUNT_REC_CUR_POMSTR,AMOUNT_PAID_CUR_POMSTR,--Finish tbl_IPPOMSTIPPOITM_SID,--Start tbl_IPPOITMPO_NUMBER_POITEM,ITEM_NUMBER_POITEM,CATEGORY_POITEM,DESCRIPTION_POITEM,VENDOR_NUMBER_POITEM,DATE_ORIGINAL,DATE_RESCHEDULED,ACCOUNT_NUMBER_POITEM,STOCK_NUMBER_POITEM,JOB_NUMBER_POITEM,RELEASE_WO_POITEM,QUANTITY_ORDERED_POITEM,QUANTITY_RECVD_POITEM,UOM_POITEM,UNIT_WEIGHT_POITEM,UNIT_COST_POITEM,EXTENDED_TOTAL_POITEM,MATERIAL_NUMBER_POITEM,COMPLETE_POITEM,LOCATION_CODE_POITEM,INSPECTION_POITEM,BOM_ITEM_POITEM,COST_ACCOUNT_POITEM,CHANGE_ORDER_POITEM,TAX_CODE_POITEM,ISSUE_CODE_POITEM,QUANTITY_INSPECT_POITEM,EXC_RATE_CURR_POITEM,UNIT_COST_CURR_POITEM,EXTENDED_TOTAL_CURR_POITEM,PLANNER_POITEM,BUYER_POITEM--Finish tbl_IPPOITMIPVENDM_SID,--Start tbl_IPVENDMVENDOR_NUMBER_VENMSTR,VENDOR_NAME_VENMSTR,ADDRESS_LINE_1_VENMSTR,ADDRESS_LINE_2_VENMSTR,ADDRESS_LINE_3_VENMSTR,CITY_VENMSTR,STATE_VENMSTR,ZIP_CODE_VENMSTR,COUNTRY_VENMSTR--Finish tbl_IPVENDMFROM tbl_IPPOMSTJOIN tbl_IPPOITMON tbl_IPPOITM.PO_NUMBER_POITEM = tbl_IPPOMST.PO_NUMBER_POMSTRJOIN tbl_IPVENDMon tbl_IPVENDM.VENDOR_NUMBER_VENMSTR = tbl_IPPOMST.VENDOR_NUMBER_POMSTRWHERE tbl_IPPOMST.PO_NUMBER_POMSTR >= @StartPoNumber ANDtbl_IPPOMST.PO_NUMBER_POMSTR <= @FinishPoNumberBasically, no rows are returned for the valid (records in database)range I enter. I have been troubleshopoting the syntax. This hasinvolved commenting out references to @FinishPoNumber so in effect Ijust pass in a valid PO Number using @StartPoNumber parameter. Thisworks in terms of returning all 76545 PO records.Can anyone help me to identify why this syntax will not return a rangeof PO records that fall between @StartPoNumber and @FinishPoNumber?Any help would be greatly appreciated.Many Thanks*rohan* & Merry Christmas!

View 2 Replies View Related

Getting Results With Stored Procedure From Single Textbox Searching Table With Multi Columns

Feb 12, 2007

I admit I am a novice to queries and stored procedures but understand stored procedures are more secure, so I am choosing to challenge myself with a stored procedure and have not been successful at all.

What I am looking for is a stored procedure which would query the single table named Hospital, which would search from a single textbox the end user would enter the text they are searching for on a Windows VB application but is dependant on a few end user selected items.

1. The end user can select a radio button either "Starts with" or "Contains"
2. The end user can select from 6 checkboxes (Hospitals, Address, City, State, Zip, Type) to limit or expand their search from the columns in the table.

The table has 17columns (CO, PARENTCO, ADDR, POBox, CITY, State, ZIP, TEL, FAX, WEBSITE, HOSP_TYPE, OWNERSHIP, BEDS, CONTACT, TITLE, Email) but I am only allowing the end user to search by the above 6 columns and need to display the above 6 columns plus the phone, fax and website columns.


After the user clicks the search button it displays the results in a listbox and the user will then select the one they want and it displays the information in another set of textboxes.

Hopefully someone can get me pointed in the right direction! Thanks for your help!

View 12 Replies View Related

Query Info Between Time Range & Date Range

Aug 16, 2006

I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!

View 6 Replies View Related

Date Selection Giving Default '1/1/1900' For NULL Values

Feb 9, 2005

'XXX_DTE' is character type which wont take NULL.

SELECT CONVERT(Datetime,XXX_DTE) FROM XXXX

I get result as : 1/1/1900

Why is it so.....

What I expect is '0000-00-00 00:00:00.000'

View 1 Replies View Related

SQL 2012 :: Use Date Trunc Or Date Function To Select Date Range For Month On Month View

Jul 29, 2015

My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see

Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table

View 9 Replies View Related

Reporting Services :: Date Range Filter Based On Date Values Returned In Report?

Aug 27, 2015

I have a QA Deployment Date field that is being returned in a custom report I created. I also found a sample date range parameter:

What I want to accomplish:

I want to select a From and To Date and filter the report to only display the rows that have the QA Deployment Date within the selected range.

For example.. I want to select From Date (8/1/2105) and To Date (8/31/2015) and I only want to return only the results that have a QA Deployment date between that selected range.

View 3 Replies View Related

Date Parameter In SSRS - Allow Drop Down For A Date Range To Be Selected

Aug 11, 2013

Date parameter. I created a report that allows a drop down for a date range to be selected. However, whenever I preview the report, I get an error. I know my error stems from my date fields being in this format "201301" , and the "date/ time" in SSRS being mm/dd/yyyy on the drop down calendar in SSRS.

I know the direction I want to go in, but just a little confused on where would I use the convert or cast function. Would it be in the data parameter itself, or a part of the query before the @start date and @End date?

View 18 Replies View Related

How To Get Latest Temperature Reading For Each Date In A Date Range

Sep 28, 2012

I have to display the last temperature reading from an activity table for all the dates in a selected date range.So if I select the date range from 09/01/2012 to 09/30/2012, the results should look like this:

Date Temperature
09/01/2012 73.5
09/02/2012 75.2
09/03/2012 76.3
09/04/2012 73.3
09/05/2012 77.0
09/06/2012 74.5
and so on.

I am using this to get the dates listed:
WITH CTE_DatesTable
AS
(
SELECT CAST('20120901' as date) AS [Date]
UNION ALL
SELECT DATEADD(dd, 1, [Date])
FROM CTE_DatesTable
WHERE DATEADD(dd, 1, [Date]) <= '20120930'
)
SELECT [Date]
FROM CTE_DatesTable

How could I get the temperature if I did a sub-query here?

View 5 Replies View Related

{RESOLVED} Date Logic - Calculating A Date Range

Jan 23, 2007

I have a report that I need to run on 2 different date ranges.

Both report's data is 2 days behind today's date.
so...
WHERE reportdate between dateadd('d',date(),-2) and dateadd('d',date(),-2)
OR SOMETHING LIKE THAT, NO BIGGIE HERE

The 2nd report is a month to date report. This is the 1 I can't figure out.
WHERE reportdate between (the first day of this month) and dateadd('d',date(),-2)

So that would look like
WHERE reportdate between 1/1/2007 and 1/21/2007

My problem is, if today is the 1st day of the month... how can I get my critiera to NOT do this
WHERE reportdaye between 2/1/2007 and 1/30/2007

Any help would be greatly appriciated!

View 2 Replies View Related

Date Picker Controls - Anyway To Limit Date Range

Jul 26, 2007

Have seen other questions here about modifying date pickers supplied by reports created in BIDS. The answer is usually NO. But this does not involve a format change, simply want to limit say to a specific year.
Any ideas?

View 4 Replies View Related

Finding Where My Date Falls In Date Range

Oct 25, 2007

Hi;

We received a Payment from a customer on '10/10/2007 10:30:00'. i am trying to calculate the commission we would receive from that payment. the commission rate can be edited. so i have to find what the commission rate was when that payment was received.


I have a CommisionAudit table that tracks changes in commission rate with the following values.

ID | Commission Change | UpdatedOn
----------------------------------------------
1 | Change from 20->25 | 03/07/2007 09:00:00
----------------------------------------------
2 | Change from 25->35 | 10/09/2007 17:00:00
----------------------------------------------
3 | Change from 35->20 | 01/10/2007 16:00:00
----------------------------------------------
4 | Change from 20->26 | 11/10/2007 10:00:00
----------------------------------------------


with this payment, as the commission rate had been changed on 01/10/2007 it would obviously be 20%(ID 3). But I need to write sql to cover all eventualities i.e. Before the first and after the last. any help would be most welcome.

View 11 Replies View Related

Stored Procedure With Date

May 7, 1999

There is a stored procedure to run each Sunday. I would like to get the order date from previous Sunday through Saturday. Right now we code as
"OrderDate Between convert(datetime, '05/02/99') AND
convert(datetime, '05/08/99') "
Each week the dates have to be manually changed. How to use the date function to make it automated?

View 2 Replies View Related

Date Stored Procedure

Jul 20, 2005

Hi all,I am trying to write a stored procedure that will delete records in atable that are older then 30 days. I am checking against a field thatis called PositionDate. Can someone point me in the right direction?TIA

View 4 Replies View Related

Sp Giving Error

Sep 13, 2006

i have to get the maximum into a output parameter. its giving error. whats the problem with this code

SET @supplier_code as EXECUTE (SELECT MAX(supplier_code)+1 AS Supp_Id FROM supplier)

suji

View 5 Replies View Related

Group By Date In Stored Procedure

Sep 12, 2007

I am trying to to a transaction count (per day) running a stored procedure that does a group by date. The problem is that since the date has a time stamp (I assume) it sees each date as a different group even if it's the same day. Is there a way to format the date in the stored procedure so that it sees all of the transactions on the same day as one or is there another way to do this.
Select count(recordid),transactiondatefrom sometable group by transactiondate
Thanks
in advance

View 1 Replies View Related

Stored Procedure And Date For Today

Dec 29, 2004

Hi there,

I am trying to ascertain how many users have registered with my site today. I am using the following stored procedure:

CREATE Procedure spGetUserCountToday
As
Return ( SELECT Count(*) FROM tblUserList WHERE role= "User" AND registerDate >= GETDATE()
)
GO


However the issue is the GetDate() function will only return those that have resgistered at the exact moment the query is run.

How can I change the GetDate to return only those users who have registered in??

Thanks in advance,

TCM

View 3 Replies View Related

Stored Procedure - Date Function

Jul 28, 2005

Hi, I ran into some problem here. The case scenerio is supposed to be like this:

- Each member can only make one appointment at any one time and only
make another appointment after the existing appointment expired.
- Each member is allowed to make an appointment at any time and must be at least 5 days in advance.

I managed to do the reservation for at least 5 days in advance but I
can't allow the member to make only one appointment. The member can
keep making appointments even though the existing appointment has not
expired. Can someone pls help? Thanks!


ALTER PROCEDURE spReserveAppt(@AppDate DATETIME, @AppTime CHAR(4), @MemNRIC CHAR(9))
AS

BEGIN
IF NOT EXISTS(SELECT MemNRIC FROM DasMember WHERE MemNRIC = @MemNRIC)
    RETURN -300

BEGIN
IF EXISTS
     (SELECT COUNT(@MemNRIC)
    FROM DasAppointment   
    WHERE (DATEDIFF(DAY, GETDATE(), @AppDate) < 5)
    GROUP BY MemNRIC
    HAVING COUNT(@MemNRIC) <> 0)
    RETURN -301

ELSE IF EXISTS
    (SELECT MemNRIC
    FROM DasAppointment   
    WHERE (DATEDIFF(DAY, @AppDate ,GETDATE()) > GETDATE()))
    RETURN -302

END
END

INSERT INTO DasAppointment(AppDate, AppTime, MemNRIC) VALUES (@AppDate, @AppTime, @MemNRIC)

IF @@ERROR <> 0
    RETURN @@ERROR

RETURN

DECLARE @status int
EXEC @status = spReserveAppt '2005-08-16', '1900', 'S1256755J'
SELECT 'Status' = @status

View 2 Replies View Related

Stored Procedure - Sql Date Select

May 1, 2006

Having a little trouble getting this one to work.
I have a stored procedure that selects items from the table...
SELECT fldDate, shortTitle, longTitle, Email,  POC, News, guidFROM tblNews
I need to change it so it selects only the year needed.  For instance, only items from 2004.
SELECT fldDate, shortTitle, longTitle, Email,  POC, News, guidFROM tblNews WHERE fldDate = @myParameter
-Say myParameter = 2004 or whatever year is needed
The table field fldDate is a datatime field that has date and time stored and I can put any parameter into the stored procedure.
How can I be sure to only get a certain year?
Thanks,
Zath

View 2 Replies View Related

Stored Procedure Creation Date

Jun 14, 2001

Is there any system stored procedure or any table that contains the creation date of
stored procedures in one database . I really want to know wich is the last stored procedure
created in a database.
Thanks.

View 1 Replies View Related

How To Get Last Processed Date Of Stored Procedure

Jul 30, 2007

Hello everybody

Help me to get the last processed date of a stored procedure.

View 6 Replies View Related

Stored Procedure Last Changed Date

Mar 5, 2004

I would like to know what stored procedures were updated recently. The only date I can see is the Creation Date. Any idea if the 'Last Change Date' is available?

View 6 Replies View Related

Date Return From Stored Procedure...help!

Jul 1, 2006

Hi,

I created this stored procedure to add 90 days to the current date, and return the new date:

CREATE PROCEDURE Get90Days
AS
SELECT DATEADD(day, 90, current_timestamp) AS "RETURNDAYS"

The procedure above returns this result:

2006-09-29 07:31:14.477

I need this procedure to return the date, only, like this:

09-29-2006

I tried using the convert switch to reformat the result to the date only, but have not been successful.

What does this stored procedure need to look like to return just the date, like this:

09-29-2006

I appreciate your help.
Thanks,
Bill

View 4 Replies View Related







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