Stored procedures - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored procedures

Author

Topic

honeytjames
Starting Member

3 Posts

Posted - 2009-10-18 : 16:31:18

Hi there,
I'm trying to write a stored proc that we need to run automatically every month. So i need to schedule a job to run a stored proc monthly.the stored proc basically contains a call to another stored proc and then 2 insert statements.
I'm not really sure how to write a stored proc.. This as far as I got:

CREATE PROCEDURE [dbo].[procMonthlyReports]

@ReportDate DateTime
AS
BEGIN
SET NOCOUNT ON;

EXEC procCharge @ReportDate

end
GO

The @ReportDate is 'yyyy-mm-dd 23:59:59' where yyyy-mm-dd is the last day of the month for the report.

when i execute this i get the following error :Error converting data type nvarchar to datetime.

What am i doing wrong here? Any help, greatly appreciated.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-18 : 16:38:50

Try to give the parameter like this for example (without -sign):
'20091031 23:59:59'

No, you're never too old to Yak'n'Roll if you're too young to die.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-10-19 : 02:57:15

nothing wrong with your parameter value 'yyyy-mm-dd 23:59:59', no need to exclude "-" sign

CREATE PROCEDURE [dbo].[procMonthlyReports]

@ReportDate DateTime
AS
BEGIN
SET NOCOUNT ON;

SELECT @ReportDate

end
GO

EXEC [dbo].[procMonthlyReports] '2009-07-25 23:59:59'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-19 : 03:30:33

quote:
Originally posted by lionofdezert

nothing wrong with your parameter value 'yyyy-mm-dd 23:59:59', no need to exclude "-" sign

CREATE PROCEDURE [dbo].[procMonthlyReports]

@ReportDate DateTime
AS
BEGIN
SET NOCOUNT ON;

SELECT @ReportDate

end
GO

EXEC [dbo].[procMonthlyReports] '2009-07-25 23:59:59'

It depends on some config...

In my environment this errors: '2009-07-25 23:59:59' and this errors not: '20090725 23:59:59'

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources