Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

QBF_Stored Procedure (Date Question)

Posted on 2003-12-08
3
Medium Priority
?
476 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 1500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

722 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