Avatar of lambc
lambc

asked on 

BETWEEN dates in dynamic sql

I get the following error when I run the following stored procedure: 'ODBC Error Code = 22008 (Datetime field overflow)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.'  It will be a miracle if I get this working, it is driving me crazy.  Thanks in anticipation of any help.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE sp_GetDaybook
@sContactIDList varchar (1000),
@sStock varchar (20),
@dtFrom datetime,
@dtTo datetime

AS

DECLARE @MySQL varchar (500)
DECLARE @MyWhere varchar (1200)
DECLARE @MyOrder varchar (100)

SET @MySQL = '
SELECT sDaybookID, sClientID, iContactID, dtComment, sStock, sAuthor, sComment
FROM   Daybook'

SET @MyWhere = '
WHERE 0 = 0'

IF @sContactIDList IS NOT NULL SET @MyWhere = @MyWhere + 'AND iContactID IN' + '(' + @sContactIDList + ')'
IF @sStock IS NOT NULL SET @MyWhere = @MyWhere + 'AND sStock LIKE' + '''' + '%' + @sStock + '%' + ''''
SET @MyWhere = @MyWhere + 'AND dtComment BETWEEN' + @dtFrom + 'AND'+ @dtTo

SET @MyOrder = '
ORDER BY dtComment Asc'

EXEC (@MySQL + @MyWhere + @MyOrder)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Microsoft SQL Server

Avatar of undefined
Last Comment
lambc
Avatar of muzzy2003
muzzy2003

The problem isn't inside the stored procedure, it's in passing in the parameters. What format are you using to pass them in? You need ideally to use an unambiguous format like:

2004-02-08 01:52:00

if you are passing dates using a string.
ASKER CERTIFIED SOLUTION
Avatar of muzzy2003
muzzy2003

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of lambc
lambc

ASKER

I am passing dtFrom and dtTo as TIMESTAMP values via Cold Fusion form.  I have changed the above line as requested by you to:

'AND dtComment BETWEEN' + '''' + CONVERT(varchar(20), @dtFrom, 120) + 'AND' + CONVERT(varchar(20), @dtTo, 120) + ''''

Thanks for that.  

I am now getting an error (syntax again) Incorrect syntax near the keyword 'ORDER'.

Avatar of lambc
lambc

ASKER

Fiddled some more and at last success.

'AND dtComment BETWEEN' + '''' + CONVERT(varchar(20), @dtFrom, 120) + ''' AND ''' + CONVERT(varchar(20), @dtTo, 120) + ''''

Needed some more single quotes and a couple of spaces.

Thanks heaps for all your help.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo