• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 507
  • Last Modified:

QBF_Stored Procedure (Date Question)

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
de_anvil
Asked:
de_anvil
  • 2
1 Solution
 
namasi_navaretnamCommented:
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
 
de_anvilAuthor Commented:
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
 
de_anvilAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now