February 8, 2004 at 2:40 pm
Hi I have a stored procedure which takes a tablename and a date as variables and runs a select on the table with those criteria.
Unfortunately it's not working for me. Any ideas appreciated.
CREATE PROCEDURE UK_busy_report
@TableName varchar(255)
, @reporteddate varchar(40)ASBEGIN
DECLARE @SQLStatement varchar(255)
DECLARE @date datetime
SELECT @reporteddate=CONVERT(datetime, @date, 103)IF @@ERROR <> 0
BEGIN Print 'ERROR'
END
ELSE
SELECT @SQLStatement = 'SELECT vendor, reporteddate,
count(vendor) FROM '
+ @TableName + 'WHERE reporteddate =
' + ''''+ convert (nvarchar(14),
@reporteddate, 101) + ''''
EXEC(@SQLStatement)ENDGO
.logic.
February 8, 2004 at 5:01 pm
Firstly I don't know why you convert your date parameter to datetime then back
to nvarchar. Why not just use the passed value as it is?
In the statement where you convert the date parameter to datetime you
had the variables in the wrong spots. You were effectively setting the @reportdate
variable to NULL. Also, there was a space missing before the WHERE keyword and
you can't have an aggregate function without a GROUP BY.
CREATE PROCEDURE UK_busy_report
@TableName varchar(255)
, @reporteddate varchar(40)
AS
BEGIN DECLARE @SQLStatement varchar(255) DECLARE @date datetime
SELECT @date = CONVERT(datetime, @reporteddate, 103)
IF @@ERROR <> 0 BEGIN Print 'ERROR' END ELSE SELECT @SQLStatement = 'vendor, reporteddate, count(vendor) FROM ' + @TableName + ' WHERE reporteddate = ''' + convert (nvarchar(14), @date, 106) + '''' + ' GROUP BY vendor, reporteddate'
EXEC(@SQLStatement)
END GO
PS: Can you edit you post so it's not so wide
--------------------
Colt 45 - the original point and click interface
February 9, 2004 at 2:03 am
Hi Phill, thanks for the reply.
When I exec that I've tried using the following format:
exec UK_busy_report EU_master_week6, '02/02/04'
I was searching the EU_master_week6 table for any dates matching the 02 February 2004?
I've also tried playing around with the convert function and changing the number from 101 through to 120
My database stored the date in a smalldatetime field using yyyy-dd-mm hh:mm:ss format.
Every time I get the same error: Server: Msg 241, Level 16, State 1, Procedure UK_busy_report, Line 7
Syntax error converting datetime from character string.
thanks for the help,
.logic.
February 9, 2004 at 7:28 am
Style is only used when converting to character.
Use
SELECT @date = CONVERT(datetime, @reporteddate)
Also the test of @@ERROR will not be used as failure in date conversion will cause Level 16 error will will stop the procedure.
Far away is close at hand in the images of elsewhere.
Anon.
February 10, 2004 at 4:55 am
Converting a string date to datetime format always yields a datetime field where the time portion is '00:00:00.000'. If your ReportedDate field on the database contains full date/time values (i.e. 02/08/2004 17:31:21.123) and you are comparing to your converted date which, based on the above statement contains 'mm/dd/yyyy 00:00:00.000' you will never get an "equals" state.
If your data contains full datetime vales (i.e. they were set using Getdate() or some other real-time function) then you will need to trim off the time portion to get a valid comparison using strings, or else compute two datetime values, ReportedDate and ReportedDate+1 and then use:
Where ReportedDate>= ' + ''''+convert(nvarchar,@date) + ''' AND ReportedDate <= ' + ''''+convert(nvarchar,@dateadd(Day,+1,@date) + ''''
This should give you something like ">= 02/04/2004 00:00:00.000 AND <= 02/05/2004 00:00:00.000" which will give you all the records with '02/04/2004' as a date.
It's unfortunate that SQL doesn't have Date and Time datatypes with the corresponding functions, but it is what it is.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply