SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Passing Dates to a Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 07/31/2000 :  05:30:26  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Pari writes "I take a date from the html form and wish to pass on to the stored procedure where the value is inserted . . .

Article Link.

Anonymous
Starting Member

0 Posts

Posted - 08/15/2000 :  11:27:18  Show Profile  Reply with Quote
Inserting dates through SQL

Assuming you want to execute the stored procedure InsertMain from the connection object conn, passing parameters held in the variables id, name, desc, syno and LDate - where LDate contains a valid date:


1. Create the funciton:

Function FormatTS(dteDate)
Dim dte
Dim Res,Temp
on error resume next

If IsDate(dteDate) Then
dte = CDate(dteDate)
Else
dte = Now()
End If

Res = Right("0000" & Year(dte),4) & "-" & _
Right("00" & Month(dte),2) & "-" & _
Right("00" & Day(dte),2) & " " & _
Right("00" & Hour(dte),2) & ":" & _
Right("00" & Minute(dte),2) & ":" & _
Right("00" & Second(dte),2)

FormatTS = "{ts '" & Res & "'}"
End Function


2. Execute the code:

conn.execute "InsertMain ('" & id & "','" & name & "','" & desc & "','" & syno & "'," & FormatTS(LDate) & ")",,4


NB: 4 = adCmdStoredProc


This uses the standard ODBC time-stamp format to pass the date.

Go to Top of Page

jhermiz
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 10/09/2004 :  14:03:14  Show Profile  Visit jhermiz's Homepage  Reply with Quote
quote:
Originally posted by AskSQLTeam

Pari writes "I take a date from the html form and wish to pass on to the stored procedure where the value is inserted . . .<BR><P>Article <a href="/item.asp?ItemID=226">Link</a>.



Isn't it @@Error ?

I think @Error will throw an error in the sproc..

try this:


CREATE PROC update_issue_by_issue_id (
					@IssueID bigint,
					@CustomerID bigint,
					@SiteID bigint=NULL,
					@CommissionID bigint=NULL,
					@IssueTypeID bigint=NULL,
					@Originator varchar(50),
					@TargetDate datetime,
					@ClosedDate varchar(40)=NULL,
					@Issue varchar(8000),
					@ModifiedBy varchar(50),
					@ModifiedOn datetime
				    )
--sproc: insert_issue
--Created By: Jon Hermiz
--Created On: October 8, 2004
--Modified By:
--Modified On:
--Reason For Modification:
--Function: To insert an issue
AS
DECLARE @CD DATETIME
SELECT @CD = CONVERT(datetime, @ClosedDate)

IF @@ERROR <> 0
 /* no closed date */
UPDATE Issue SET
		CustomerID=@CustomerID,
		SiteID=@SiteID,
		CommissionID=@CommissionID,
		IssueTypeID=@IssueTypeID,
		Originator=@Originator,
		TargetDate=@TargetDate,
		Issue=@Issue,
		ModifiedBy=@ModifiedBy,
		ModifiedOn=@ModifiedOn
WHERE IssueID=@IssueID
ELSE
/*with closed date*/
UPDATE Issue SET
		CustomerID=@CustomerID,
		SiteID=@SiteID,
		CommissionID=@CommissionID,
		IssueTypeID=@IssueTypeID,
		Originator=@Originator,
		TargetDate=@TargetDate,
		ClosedDate=@CD,
		Issue=@Issue,
		ModifiedBy=@ModifiedBy,
		ModifiedOn=@ModifiedOn
WHERE IssueID=@IssueID
GO


Go to Top of Page

Jonu2
Starting Member

Australia
2 Posts

Posted - 11/23/2009 :  20:11:18  Show Profile  Reply with Quote
Hi, I'm very new to Sql and Crystal Reporting but am trying to enhance a report so that if no date range is input then it will default to a fromdate of six months previous and a todate of six months in the future.
The sp used starts with the below code and I only changed the last bit highlighted in red. When I run it, it gives me an error indicating that it doesn't know what DATETIME is - it doesn't seem to recognise DATETIME. I know I'm probably doing something very basic worng but I'd really appreciate being pointed in the right direction please, many thanks:

ALTER procedure [dbo].[NCS_AuditReport_Sp]
@ServiceCustomerID nvarchar(30),
@ResourceID nvarchar(30),-- Auditor
@Sector varchar(40), -- Customer Group
@State nvarchar(16), -- ServiceStateCode
@TaskName varchar(100),
@WorkTypeCode varchar(1000),
@FromDate datetime,
@ToDate datetime,
@Standard nvarchar(20) --MOD005

as

/*
** Confidential Information
** Limited Distribution of Authorized Persons Only
** Created 2005 and Protected as Unpublished Work
** Under the U.S. Copyright Act of 1976
** Copyright (c) 2001-2005 Epicor Software Corporation
** All Rights Reserved
**
** Rev. Name Date Reference Description
** ****** ********** ********** ************** ********************
** MOD001 TJK 2007-08-28 N/A Re-write the whole thing
** MOD002 TJK 2007-11-30 N/A Correct the join to the Client Infopath tables
** MOD003 Marketta R 2009-01-19 Change Division codes to names
**
** EXAMPLE Procedure Call

EXEC "NCS_AuditReport_Sp"
NULL,
'DPOWER',
NULL,
NULL,
NULL,
NULL,
'2007-07-01 00:00:000',
'2007-08-31 00:00:000',
NULL

EXEC "NCS_AuditReport_Sp"
'12023000',
'DPOWER',
'522',
'SA',
'EMS',
'40QAS',
'2007-07-01 00:00:000',
'2007-08-31 00:00:000',
'EMS'

*/


-- Creating a temp table to hold records for the audit report
create table #AuditReport (
ProjectCode varchar(40),
ServiceCustomerID nvarchar(30),
ServiceCustomerName nvarchar(240),
BillToID nvarchar(30),
BillToName nvarchar(240),
Status char,
ServiceStandard nvarchar(40),
ServiceStateCode nvarchar(16),
SectorCode nvarchar(12),
SectorName nvarchar(60),
ClientManagerID nvarchar(30),
ClientManagerFirstName varchar(120),
ClientManagerLastName varchar(120),
TaskUID int,
TaskName nvarchar(120),
PlannedHours decimal(8,2),
BookedHours decimal(8,2),
WorkTypeCode varchar(24),
WorkTypeDesc varchar(60),
StartDate datetime,
EndDate datetime,
CompleteFlag int,
City nvarchar(50),
ProjectPrimaryResource varchar(120), --MOD001
)

-- MOD001 Update client manager name
select distinct client_code, client_manager
into #ClientManager
FROM ncs_client_certification (NOLOCK)
WHERE client_manager IS NOT NULL
--MOD007 WHERE Client_code like @ServiceCustomer

-- Get WHERE clause parameter
declare @ServiceCustomer nvarchar(100),
@Auditor nvarchar(100),
@CustomerGroup varchar(100),
@ServiceState nvarchar(100),
@TaskDesc varchar(2000),
@WorkType nvarchar(200),
@Stand nvarchar(100)

-- If dates are blank then set dates as 6 months ago to 6 months forward

if (@FromDate = 0 or @FromDate ='' or @FromDate is null or @FromDate = '1900-01-01 00:00:00.000')
and (@ToDate = 0 or @ToDate ='' or @ToDate is null or @ToDate = '1900-01-01 00:00:00.000')
begin
select dateadd(month,-6,datetime) as StartDate
select dateadd(month,6,datetime) as EndDate
end
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000