Solved

QBF_Stored Procedure (Date Question)

Posted on 2003-12-08
3
453 Views
Last Modified: 2008-03-04
I am creating a stored procedure for a Query by Form form to load a report.  I have set most of the variables correctly and everything works just fine.  However, I need to add two date parameters to form a BETWEEN date filter.  I put in the code and I get an error message:

SYNTAX ERROR converting DATETIME from Character string.  Since this is the FIRST stored procedure I am writing I need some guidance to help me through.  Here is the code:


ALTER PROCEDURE QBF_Courses

--Create the Input Parameters

      @Category char(2),  @Instructor char(20), @County char(20), @Town char(20), @Course char(12), @Beginning_Date datetime, @Ending_Date datetime




      As
      Declare @SQLString varchar(1000)
      Declare @SelectList varchar(1000)
      Declare @SelectHaving datetime

      SET NOCOUNT ON

      SELECT @SelectList = 'Select * FROM qryCourseReportView'
      
      SELECT @SelectHaving = + 'HAVING COURSEDATE BETWEEN ''' + Convert(varchar(20), @Beginning_Date) + '''  AND ''' + Convert(varchar(20),@Ending_Date) + ''''
      --SELECT @SelectHaving = + 'HAVING COURSEDATE BETWEEN @Beginning_Date AND @Ending_Date'


--Check the first variable
--Check to see if Category IS NOT NULL
--If NOT NULL , begin to construct the WHERE clause.

            If @Category IS NOT NULL
                              
            Begin
            
                  SELECT @SQLString = 'WHERE CategoryCode = ''' + @Category + ''''

            END

--Check the second variable
--Check to see if Intructor IS NOT NULL
--IF NOT NULL, continue with the WHERE Clause construction

      IF @Instructor IS NOT NULL

      BEGIN

            IF @SQLString Is NOT NULL

            BEGIN
                  SELECT @SQLString = @SQLSTRING + ' AND INSTCODE = ''' + @Instructor + ''''

             END

            ELSE
      
                  SELECT @SQLString = 'WHERE INSTCODE = ''' + @Instructor + ''''

      END
      

--Check the Third variable
--Check to see if County IS NOT NULL
--IF NOT NULL, continue with the WHERE Clause construction

      IF @County IS NOT NULL

      BEGIN
      
            IF @SQLString IS NOT NULL

            BEGIN

                  SELECT @SQLString = @SQLString + ' AND COUNTY = ''' + @County + ''''

            END

            ELSE
                  SELECT @SQLString = 'WHERE COUNTY = ''' + @County + ''''

                  
      END

--Check the Forth variable
--Check to see if TOWN IS NOT NULL
--IF NOT NULL, continue with the WHERE Clause construction

      IF @Town IS NOT NULL

      BEGIN
      
            IF @SQLString IS NOT NULL

            BEGIN

                  SELECT @SQLString = @SQLString + ' AND TOWN = ''' + @Town + ''''

            END

            ELSE
                  SELECT @SQLString = 'WHERE TOWN = ''' + @Town + ''''

                  
      END

--Check the Fifth variable
--Check to see if Course IS NOT NULL
--IF NOT NULL, continue with the WHERE Clause construction

      IF @Course IS NOT NULL

      BEGIN
      
            IF @SQLString IS NOT NULL

            BEGIN

                  SELECT @SQLString = @SQLString + ' AND COURSECODE = ''' + @Course + ''''

            END

            ELSE
                  SELECT @SQLString = 'WHERE COURSECODE = ''' + @Course + ''''

                  
      END




--Concatenate the SELECT and the WHERE clause together
--unless all three parameters are null in which case return
--@SelectList by itself without criteria.

      IF @Category IS NULL AND @Instructor IS NULL AND @County IS NULL AND @Town IS NULL AND @Course IS NULL

      BEGIN
            SELECT @SelectList = @SelectList + ' ' + @SelectHaving

      END

      ELSE
      
      BEGIN
            SELECT @SelectList = @SelectList + ' ' + @SQLSTRING + ' ' + @SelectHaving
      END

--Execute the SQL statement.
EXECUTE(@SELECTLIST)
0
Comment
Question by:de_anvil
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 500 total points
ID: 9900424
You will need something like,

If @Beginning_Date datetime is not null and @Ending_Date datetime is not null
begin
     
   IF @SQLString IS NOT NULL
         SELECT @SQLString = @SQLString + ' AND coursedate between  ''' + @Beginning_Date  + '''  and ''' + @Ending_Date  +''''
     ELSE
        SELECT @SQLString = 'WHERE coursedate between  ''' + @Beginning_Date  + '''  and ''' + @Ending_Date  +''''

end

0
 
LVL 1

Author Comment

by:de_anvil
ID: 9903987
namasi_navaretnam

This does not work as I am still getting the error:

"SYNTAX ERROR converting DATETIME from Character string"

If @Beginning_Date datetime is not null and @Ending_Date datetime is not null
begin
     
   IF @SQLString IS NOT NULL
         SELECT @SQLString = @SQLString + ' AND coursedate between  ''' + @Beginning_Date  + '''  and ''' + @Ending_Date  +''''
     ELSE
        SELECT @SQLString = 'WHERE coursedate between  ''' + @Beginning_Date  + '''  and ''' + @Ending_Date  +''''

end
0
 
LVL 1

Author Comment

by:de_anvil
ID: 9905264
namasi_navaretnam,

I accepted your answer as it did in fact point in the right direction for solving my problem.  Just had to add a CONVERT to the code for it to work.  Final working code is as follows for those who want to see:

      IF @SQLString IS NOT NULL

      SELECT @SQLString = @SQLString + ' AND COURSEDATE between ''' + CONVERT(varchar(20), @Beginning_Date) + ''' and ''' + CONVERT(varchar(20), @Ending_Date) +''''

      ELSE

      SELECT @SQLString = ' WHERE COURSEDATE between ''' + CONVERT(varchar(20), @Beginning_Date) + ''' and ''' + CONVERT(varchar(20), @Ending_Date) +''''

      END


Thanks for the help.  Now all I have to do is create the form and attach the stored procedure to the report, set input parameters for the report and all should be well.  At least I hope.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now