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, guid
FROM 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, guid
FROM 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
ADVERTISEMENT
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View 11 Replies
View Related
Nov 14, 2014
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
View 1 Replies
View Related
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
Mar 13, 2012
I currently have a stored procedure that selects sales entries from a table and inserts them into a temp table. To execute this procedure you must enter the store number and the dates required.
What I am looking to do is run this procedure daily for one store daily to load the previous days sales into the temp table.
I have tried using the following
DECLARE @dt DATETIME
SET @dt = CAST(CAST(MONTH(getdate())as varchar) + '-' +
Cast(DAY(GetDate()-1)as varchar)+ '-' +
Cast(YEAR(GetDate())as varchar)as DateTime)
set @sql = @sql + ' and hd.TXHD_START_DATE between ''''' + @dt + ''''' and ''''' + @dt + '23595999+9999'''''
But i am getting an error
Syntax error converting datetime from character string.
View 4 Replies
View Related
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
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
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
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
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
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
Jul 30, 2007
Hello everybody
Help me to get the last processed date of a stored procedure.
View 6 Replies
View Related
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
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
Sep 17, 2007
I have a stored date in my database, example 12 October 2000 which is a start date - now i would like to retrieve this data and the number of years and months from the current date (that is today - i am using the getdate() method)...how can i get the number of years and months passed? Thanks
View 3 Replies
View Related
Dec 17, 2007
The following is part of my stored procedure where I could use some help:If I remark out the two lines regarding dates, it runs fine. The dateparameter is passed from a VB app as a text value in the format mm/dd/yy.Thanks for any help.CharlieSELECT * FROM dbo.TWQRHISTEARDOWN_HEADER where(WQR_TDH_MODELYEAR_T=@Model_Year OR @Model_Year IS NULL)AND (WQR_TDH_OFFLINEDT_Y>=@Start_Date OR @Start_Date IS NULL)AND (WQR_TDH_OFFLINEDT_Y<=@Stop_Date OR @Stop_Date IS NULL)
View 1 Replies
View Related
Nov 13, 2006
Dear friends,
I have a stored procedure that returns some fiels. One of the fields is a datetime type.
The field return in the follow format : 2006-11-13 0:00:00
How can I return only 2006-11-13? How can I use the format function?
regards!!!
View 10 Replies
View Related
May 25, 2008
Hi, I want to set the max and min of a query to the variable @Value1 and @Value2. However, when I do the following, both @Value1 and @Value2 pointed to the latest data. Do I have to use a subquery to do that or is that possible to do it directly?
DECLARE @Value1 DECIMAL(7,2)
DECLARE @Value2 DECIMAL(7,2)
SELECT TOP 20 @Value1=MIN(Value1), @Value2=MAX(Value2)FROM OrdersGROUP BY OrderID, Date
View 9 Replies
View Related
May 4, 2006
I have the stored procedure snipet below and it does not return anything.
What am I doing wrong here? I works ok if I need all the records for the dataset and don't use the WHERE and LIKE.
CREATE PROCEDURE dbo.search
@Field VARCHAR (20), @KeyWord VARCHAR (200), @errCode INT OUTPUT
ASBEGIN
DECLARE @guid uniqueidentifier DECLARE @subject NVARCHAR SELECT @guid = guid, @subject = subjectFROM myTable WHERE @Field LIKE @KeyWord
SELECT guid, subjectFROM myTable WHERE @Field LIKE @KeyWord
Thanks,
Zath
View 9 Replies
View Related
Jan 12, 2001
In Informix you can use a stored procedure as part of a SELECT statement:
SELECT ord_num, ord_date, ship_date, business_day_diff(ord_date, ship_date)
FROM order_table
business_day_diff is a stored procedure that uses a custom calendar to compute
business days. In fact, Informix stored procedures can return any data type. Apparently SQL Server stored procedures cannot be used in a SELECT like this.
Does anyone know of a straightforward way to accomplish the same purpose?
View 2 Replies
View Related
Aug 12, 2007
Say I have the following stored procedure that takes in 1 parameter, and to call it I would do this: exec stored_procedure_name 'param'
And it returns a result with 3 fields: column1, column2, column3
Now I'm only interested in getting the results for column1, is there a way to call the stored procedure to only return column1?
I tried this: select column1 from (exec stored_procedure_name 'param')
It doesnt' work...
View 2 Replies
View Related
Dec 21, 2004
Hello!
How do you use the value of one select statement within another select statement inside the same stored proc?
Here is my scenario: I need a row id from one select statement to make another select statement. I figured instead of making two calls from within my code I could just call one stored procedure which would have to be faster!
I have written plenty of select statement sp's but this is the first time I ever needed to do something like this.
Any suggestions?
View 7 Replies
View Related
Oct 13, 2005
How could I use SELECT TOP in stored procedure
For ex I want convert this line to Stored procedure
"Select Top "&intArticles&" * From tblArticles WHERE published = 1 ORDER BY Adate DESC, Atime DESC;"
I dont want use ROWCOUNT
because its not working good it will not show some data of some fields
for example if I have Subject and Body fields, in results it does not show body content, and it will show just subject :confused:
this is my current code
CREATE PROCEDURE [dbo].[SP_Articles]
(
@Articles int
)
AS
SET ROWCOUNT @Articles
SELECT tblArticles.*
FROM tblArticles
WHERE published = 1 ORDER BY Adate DESC;
SET ROWCOUNT 0
GO
and I use this code to call that from ASP
strSQL = "EXECUTE SP_Articles @Articles = " & intArticles
rsArt.CursorType = 1
rsArt.Open strSQL, strCon
so just tell me how to use TOP in stored procedure
Thanks
View 9 Replies
View Related
Jan 30, 2004
Hi everybody,
How can I select records from a SP?
For example:
My SP is the following:
CREATE PROCEDURE [dbo].[spExample]
AS
Declare @SELECT_Text AS varchar(500)
Set @SELECT_Text='SELECT * FROM dbo.ExampleTable'
exec(@SQL_Text)
GO
I want to select from it:
SELECT *
FROM dbo.spExample
It doesn't work. Then how can I use the result of the SP???
View 2 Replies
View Related
Mar 16, 2004
Hey all! I have a quick question. Is it possible do formulate a select query where one of the tables is a recordset returned from a stored procedure? If so, what's the syntax?
In other words, if I have a stored proc whose last command is:
selectt.[Region_Code],
t.[Country_Code],
sum([Total]) as [Total]
from#tmp t
group by[Region_Code],
[Country_Code]
order by[Region_Code],
[Country_Code]
Then can I somehow get a handle on that cursor and join it to another table etc? Thanks!
View 5 Replies
View Related
Sep 19, 2013
I have a stored procedure that has many select statements in it. I want to call and execute the procedure in my c# code and put data returned by each select statement into separate grids. I could just write the select into the c# code and then execute as a dataset making that dataset the datasource for whatever grid but that defeats the purpose of stored procedures. Can I capture the data returned by each select in the stored procedure and have it put into a grid when the c# code is ran.
View 4 Replies
View Related
Jul 20, 2005
HelloI have many different tables, that most have the same structure.. they allhave some primary key, some fields and mylinkid ... so what I want to makeis to create a stored procedure, where I can pass the table-name, primarykey, and the new mylinkid, what should happen is, that the procedure copiesthe entrie, where i passed the id, to a new row and the mylink-id should getthe new value...example:mytable 1id=1 (primary key autoincrement)mylinkid=233field1=asdffield2=blablai call my procedure like: duplicate_entry 'mytable',1,4the result should then beid=2 (primary key/autincrement)mylinkid=4field1=asdffield2=blablaI was thinking to make a Stored Procedure, that copies the entier row with aselect * into mytable (SELECT * from mytable where id=1) and afterwards iupdate the mylinkid ..... but there i have problem with the primary key.The other solution that will work, but I won't like is to get with thesp_fields all fields from that specific table, build the select into withthe correct fields without the id-field... that should work, but was askingmyself if there is somethign better...Has someone a good idea for that?Thanks
View 6 Replies
View Related
Jul 14, 2006
I have code simililar to the following in a stored procedure:
CREATE PROCEDURE GetGroupMembers
(
@GroupID INT
)
AS
SELECT TMembers.MemberID, VCurrentMemberGroups.GroupID, THonorsMembers.HonorID
FROM THonorsMembers LEFT OUTER JOIN
TMembers ON THonorsMembers.MemberID= TMembers.MemberID LEFT OUTER JOIN
VCurrentMemberGroups ON TMembers.MemberID= VCurrentMemberGroups.MemberID
WHERE (VCurrentMemberGroups.GroupID = @intGroupID) AND TMembers.DeleteDate IS NULL
ORDER BY TMembers.MemberID
RETURN
GO
When I call this stored procedure in Query Analyzer, it takes two minutes to execute. The web pages that rely on it time out before that. If I copy just the select statement and substitute the group I'm working with for @GroupID, it takes less than a second for the statement to run and display results.
Can anyone tell me why a select statement called through a stored procedure would take minutes longer than one ran from Query Analyzer? I have many other stored procedures that are just select statements like this with a variable or three and they have no problems completing execution in a timely manner.
View 3 Replies
View Related
Jan 8, 2008
I have a complex stored procedure that returns one or more nvarchar values given a syntax similar to this:
sp_MyProcedure 'Param1', 'Param2'
My issue is that the end goal is to get these values into a SQL query that I can call directly from something like Excel (no VBA or anything can be used). So far, I have come up with this:
********************************************************************************************************
CREATE TABLE [dbo].[#tmpTable] ([RetValue] NVARCHAR(30))
INSERT INTO [#tmpTable]
EXEC sp_MyProcedure 'Param1', 'Param2'
SELECT * FROM AnotherTable
WHERE (AnotherTable.ID IN (select * from [#tmpTable])) AND (AnotherTable.Year>2007)
drop table [#tmpTable]
********************************************************************************************************
That works, but it is incredibly sloppy once I have need to make 4 or 5 temp tables like that and then select from all of them inside the same query. So does anyone have any suggestions? Can I use a function to help out?
Thanks in advance,
JimmyR
View 7 Replies
View Related
Apr 20, 2007
hi everyone,
in SQLEXPRESS if I execute
'use SqlTrackingDb exec dbo.GetName'
I can see all entries in GetName.
But I do not know if the following select statement is possible for a stored procedure in e.g. visual studio:
select * from dbo.GetName where Id = "001";
Any ideas?
Thanks in advance,
best regards bg
View 10 Replies
View Related
Aug 3, 2006
on SQL Server 2000
They show only Create date
but I need know update date
because I install my system on customer's site and solve problem on customer site
and I can't bring all stored procedure back to my office and restore all stored
because of my database have two projects.
Please Help me.....
View 2 Replies
View Related
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
Oct 27, 1998
I need to write a sp to fill a date field, if another field in another table is true. need the date to reflect todays date(the date the field was marked true). I know this is an easy one but I am over thinking it. please help.
View 2 Replies
View Related
