I am getting this error in the SP shown below and don't see what is wrong?
Syntax error converting character string to smalldatetime data type.
The complete output is as follows:
---
DECLARE @.RC int
DECLARE @.Class char(2)
DECLARE @.StartDate datetime
DECLARE @.EndDate datetime
DECLARE @.Period varchar(10)
SELECT @.Class = 'SW'
SELECT @.StartDate = '2/5/2005'
SELECT @.EndDate = '2/13/2005'
SELECT @.Period = 'Test'
EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
@.Period
DECLARE @.PrnLine nvarchar(4000)
PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
PRINT @.PrnLine
---
=============== SP Code ================
@.Class As char(2),
@.StartDate As SmallDateTime,
@.EndDate As SmallDateTime,
@.Period As Varchar(10)
As
Set NOCOUNT ON
DECLARE
@.strSQL As varchar(1000)
SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
''' + Cast(@.EndDate As SmallDateTime) + ''
Print @.strSQL
INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
exec(@.strSQL)Maybe your system is set up for UK English, or some other regional settings,
or some other language.
How about we try a sensible and unambiguous date format, like YYYYMMDD.
SELECT @.startDate = '20050205', @.endDate = '20050213'
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> wrote in message
news:O9NBn5OKFHA.3420@.tk2msftngp13.phx.gbl...
> I am getting this error in the SP shown below and don't see what is wrong?
> Syntax error converting character string to smalldatetime data type.
> The complete output is as follows:
> ---
> DECLARE @.RC int
> DECLARE @.Class char(2)
> DECLARE @.StartDate datetime
> DECLARE @.EndDate datetime
> DECLARE @.Period varchar(10)
> SELECT @.Class = 'SW'
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> SELECT @.Period = 'Test'
> EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
> @.Period
> DECLARE @.PrnLine nvarchar(4000)
> PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
> SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
> PRINT @.PrnLine
> ---
> =============== SP Code ================
> @.Class As char(2),
> @.StartDate As SmallDateTime,
> @.EndDate As SmallDateTime,
> @.Period As Varchar(10)
> As
> Set NOCOUNT ON
> DECLARE
> @.strSQL As varchar(1000)
> SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
> Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
> ''' + Cast(@.EndDate As SmallDateTime) + ''
> Print @.strSQL
> INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
> exec(@.strSQL)
>|||Well for one thing You have "=>" In there, and that's wrong, it should be
">=".
Second, '2/13/2005' _COULD_ be getting interpreted as 2nd day of 13th
month... depending on server settings... A format that always works is
CCYYMMDD, or, for Feb 13, 2005,
'20050213'
try changing the string literals
SELECT @.StartDate = '2/5/2005'
SELECT @.EndDate = '2/13/2005'
to
SELECT @.StartDate = '20050205'
SELECT @.EndDate = '20050213'
and see if it works then...
"Wayne Wengert" wrote:
> I am getting this error in the SP shown below and don't see what is wrong?
> Syntax error converting character string to smalldatetime data type.
> The complete output is as follows:
> ---
> DECLARE @.RC int
> DECLARE @.Class char(2)
> DECLARE @.StartDate datetime
> DECLARE @.EndDate datetime
> DECLARE @.Period varchar(10)
> SELECT @.Class = 'SW'
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> SELECT @.Period = 'Test'
> EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
> @.Period
> DECLARE @.PrnLine nvarchar(4000)
> PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
> SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
> PRINT @.PrnLine
> ---
> =============== SP Code ================
> @.Class As char(2),
> @.StartDate As SmallDateTime,
> @.EndDate As SmallDateTime,
> @.Period As Varchar(10)
> As
> Set NOCOUNT ON
> DECLARE
> @.strSQL As varchar(1000)
> SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
> Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
> ''' + Cast(@.EndDate As SmallDateTime) + ''
> Print @.strSQL
> INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
> exec(@.strSQL)
>
>|||Wayne Wengert wrote:
> I am getting this error in the SP shown below and don't see what is wrong?
> Syntax error converting character string to smalldatetime data type.
> The complete output is as follows:
> ---
> DECLARE @.RC int
> DECLARE @.Class char(2)
> DECLARE @.StartDate datetime
> DECLARE @.EndDate datetime
> DECLARE @.Period varchar(10)
> SELECT @.Class = 'SW'
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> SELECT @.Period = 'Test'
> EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
> @.Period
> DECLARE @.PrnLine nvarchar(4000)
> PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
> SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
> PRINT @.PrnLine
> ---
> =============== SP Code ================
> @.Class As char(2),
> @.StartDate As SmallDateTime,
> @.EndDate As SmallDateTime,
> @.Period As Varchar(10)
> As
> Set NOCOUNT ON
> DECLARE
> @.strSQL As varchar(1000)
> SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
> Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
> ''' + Cast(@.EndDate As SmallDateTime) + ''
> Print @.strSQL
> INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
> exec(@.strSQL)
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Your WHERE clause should be like this:
WHERE Showdate >= ''' + Convert(char(8),@.StartDate,112) + '''
AND ShowDate <= ''' + Convert(char(8), @.EndDate, 112) + ''''
Since you've already declared the parameters @.StartDate & @.EndDate as
SmallDateTime data types you don't have to do it again w/ the Cast()
function. What you have to do, since you're putting the date values in
a string, is convert them to string data types. In my example I used
CHAR(8) to just get a date like this '20040314'.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjYjzoechKqOuFEgEQKVLwCg9K2hY2Pnsi9Y
gASMQFvboh8aV1cAnAka
Fnny1XBrRp8n15Q7xe4Mm6VR
=GT97
--END PGP SIGNATURE--|||And Oh, replace "=>" and "=<" with ">=", and "<="
"Wayne Wengert" wrote:
> I am getting this error in the SP shown below and don't see what is wrong?
> Syntax error converting character string to smalldatetime data type.
> The complete output is as follows:
> ---
> DECLARE @.RC int
> DECLARE @.Class char(2)
> DECLARE @.StartDate datetime
> DECLARE @.EndDate datetime
> DECLARE @.Period varchar(10)
> SELECT @.Class = 'SW'
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> SELECT @.Period = 'Test'
> EXEC @.RC = [DB_136571].[dbo].[AddMinMax] @.Class, @.StartDate, @.EndDate,
> @.Period
> DECLARE @.PrnLine nvarchar(4000)
> PRINT 'Stored Procedure: DB_136571.dbo.AddMinMax'
> SELECT @.PrnLine = ' Return Code = ' + CONVERT(nvarchar, @.RC)
> PRINT @.PrnLine
> ---
> =============== SP Code ================
> @.Class As char(2),
> @.StartDate As SmallDateTime,
> @.EndDate As SmallDateTime,
> @.Period As Varchar(10)
> As
> Set NOCOUNT ON
> DECLARE
> @.strSQL As varchar(1000)
> SET @.strSQL = 'Select UnitName, UnitClass, Max(GrossScore) From YTD WHERE
> Showdate => ''' + Cast(@.startdate As SmallDateTime) + ''' AND ShowDate =<
> ''' + Cast(@.EndDate As SmallDateTime) + ''
> Print @.strSQL
> INSERT INTO MinMax(UnitName, UnitClass, Maxscore)
> exec(@.strSQL)
>
>|||Thanks - I figured that out (finally)
Wayne
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:849DC6A0-15EF-4616-BC47-555F941C4199@.microsoft.com...
> Well for one thing You have "=>" In there, and that's wrong, it should
be
> ">=".
> Second, '2/13/2005' _COULD_ be getting interpreted as 2nd day of 13th
> month... depending on server settings... A format that always works is
> CCYYMMDD, or, for Feb 13, 2005,
> '20050213'
> try changing the string literals
> SELECT @.StartDate = '2/5/2005'
> SELECT @.EndDate = '2/13/2005'
> to
> SELECT @.StartDate = '20050205'
> SELECT @.EndDate = '20050213'
> and see if it works then...
>
> "Wayne Wengert" wrote:
>
wrong?
WHERE
=<|||Thanks - that was what I forgot!
Wayne
"MGFoster" <me@.privacy.com> wrote in message
news:6tpZd.10908$cN6.9661@.newsread1.news.pas.earthlink.net...
> Wayne Wengert wrote:
wrong?
WHERE
=<
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Your WHERE clause should be like this:
> WHERE Showdate >= ''' + Convert(char(8),@.StartDate,112) + '''
> AND ShowDate <= ''' + Convert(char(8), @.EndDate, 112) + ''''
> Since you've already declared the parameters @.StartDate & @.EndDate as
> SmallDateTime data types you don't have to do it again w/ the Cast()
> function. What you have to do, since you're putting the date values in
> a string, is convert them to string data types. In my example I used
> CHAR(8) to just get a date like this '20040314'.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQjYjzoechKqOuFEgEQKVLwCg9K2hY2Pnsi9Y
gASMQFvboh8aV1cAnAka
> Fnny1XBrRp8n15Q7xe4Mm6VR
> =GT97
> --END PGP SIGNATURE--|||
> Your WHERE clause should be like this:
> WHERE Showdate >= ''' + Convert(char(8),@.StartDate,112) + '''
> AND ShowDate <= ''' + Convert(char(8), @.EndDate, 112) + ''''
This still could lead to an error if his settings are, say, UK English, and
he says
SET @.startDate = '2/16/2005'
> Since you've already declared the parameters @.StartDate & @.EndDate as
> SmallDateTime data types you don't have to do it again w/ the Cast()
Neither do you have to do a CONVERT at all in this case (if he uses YYYYMMDD
in his SET/SELECT then it's already in 112 format), and nor do you have to
surround the date value with strings like you did. This will be sufficient:
WHERE ShowDate >= @.startDate
Finally, more for Wayne than the others, be careful how you define the end
date. If you say <= <somedate_notime> you will include rows with a value of
midnight on that day, but not 12:01 AM or 3:45 PM. If you only have
midnight timestamps in the data then it's no big deal, but if you don't
constrain the data, you're better off using < (@.endDate + 1).
A
