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
Solved

QBF_Stored Procedure (Date Question)

Posted on 2003-12-08
3
460 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

809 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