datetime error [xx(] - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

datetime error [xx(]

Author

Topic

ritudb
Starting Member

7 Posts

Posted - 2009-03-25 : 08:01:21

CREATE PROCEDURE [dbo].[mytest]
@eDate datetime,
@strPrefix VARCHAR(50),
@strTableName VARCHAR(50)
AS
BEGIN
DECLARE
@sql varchar(1000)

SET @sql = 'SELECT c.'+ @strPrefix+ '_id, c.' +
@strPrefix+ '_name
FROM mainAdmin a
INNER JOIN '+ @strTableName+' c ON
a.parent_id=c.'+ @strPrefix+'_id
WHERE a.expiryDate <'+ @eDate +'AND a.expiryDate != "01/01/1900"
AND c.'+@strPrefix+'_intro IS NOT NULL AND a.visible ="on"
ORDER BY publishDate DESC'

EXEC (@sql)
END

This is my stored procedure but when I try to execute it as

exec mytest '25/03/2009','issue', 'mainIssues'

I receive this error message:

Msg 241, Level 16, State 1, Procedure mytest, Line 10
Conversion failed when converting datetime from character string.

Please help me how to remove this error.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-25 : 08:17:16

Do NOT use double quote character!
Use double single quote character.

E 12°55'05.63"
N 56°04'39.26"

ritudb
Starting Member

7 Posts

Posted - 2009-03-25 : 08:28:35

quote:
Originally posted by Peso

Do NOT use double quote character!
Use double single quote character.

E 12°55'05.63"
N 56°04'39.26"

Thanx for ur reply. i changed all double quotes with double single quotes

see below:
SET @sql = 'SELECT c.'+ @strPrefix+ '_id, c.' + @strPrefix+ '_name
FROM mainAdmin a
INNER JOIN '+ @strTableName+' c ON a.parent_id=c.'+ @strPrefix+'_id
WHERE a.expiryDate <'+ @eDate +'AND a.expiryDate != ''01/01/1900''
AND c.'+@strPrefix+'_intro IS NOT NULL AND a.visible =''on''
ORDER BY publishDate DESC'

but still same error :

Msg 241, Level 16, State 1, Procedure mytest, Line 10
Conversion failed when converting datetime from character string.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-25 : 08:35:46

If you replace EXEC with print, how do the query string look like?
Can you spot some obvious mistakes?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-25 : 08:40:14

[code]SET @SQL = '
SELECT c.' + QUOTENAME(@strPrefix + '_id') + ',
c.' + QUOTENAME(@strPrefix + '_name') + '
FROM mainAdmin AS a
INNER JOIN ' + QUOTENAME(@strTableName) + ' AS c ON c.' + QUOTENAME(@strPrefix + '_id') + ' = a.parent_id
WHERE a.expiryDate < ' + QUOTENAME(CONVERT(VARCHAR(30), @eDate, 121), '''') + '
AND a.expiryDate <> ''1900-01-01''
AND c.' + QUOTENAME(@strPrefix + '_intro') + ' IS NOT NULL
AND a.visible = ''on''
ORDER BY publishDate DESC'

PRINT @SQL[/code]

E 12°55'05.63"
N 56°04'39.26"

ritudb
Starting Member

7 Posts

Posted - 2009-03-25 : 08:57:33

quote:

Originally posted by Peso

SET	@SQL = '
SELECT		c.' + QUOTENAME(@strPrefix + '_id') + ',
		c.' + QUOTENAME(@strPrefix + '_name') + '
FROM		mainAdmin AS a 
INNER JOIN	' + QUOTENAME(@strTableName) + ' AS c ON c.' + QUOTENAME(@strPrefix + '_id') + ' = a.parent_id
WHERE		a.expiryDate < ' + QUOTENAME(CONVERT(VARCHAR(30), @eDate, 121), '''') + '
		AND a.expiryDate <> ''1900-01-01''
		AND c.' + QUOTENAME(@strPrefix + '_intro') + ' IS NOT NULL
		AND a.visible = ''on''
ORDER BY	publishDate DESC'

PRINT	@SQL

E 12°55'05.63"
N 56°04'39.26"

THANX AGAIN...

I replace it with print and got this
SELECT c.[issue_id],
c.[issue_name]
FROM mainAdmin AS a
INNER JOIN [mainIssues] AS c ON c.[issue_id] = a.parent_id
WHERE a.expiryDate < '2009-03-25 00:00:00.000'
AND a.expiryDate <> '1900-01-01'
AND c.[issue_intro] IS NOT NULL
AND a.visible = 'on'
ORDER BY publishDate DESC

AND THEN WHEN I RAN THIS QUERY I got this:-
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-25 : 08:59:21

What datatype is @eDate?

E 12°55'05.63"
N 56°04'39.26"

ritudb
Starting Member

7 Posts

Posted - 2009-03-25 : 09:03:48

quote:
Originally posted by Peso

What datatype is @eDate?

E 12°55'05.63"
N 56°04'39.26"

@eDate DATETIME

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-25 : 09:06:44

Works great for me

DECLARE	@SQL VARCHAR(1000),
	@strPrefix VARCHAR(200),
	@strTableName VARCHAR(200),
	@eDate DATETIME

SELECT	@strPrefix = 'Peso',
	@strTableName = 'SQLTeam',
	@eDate = GETDATE()

SET	@SQL = '
SELECT		c.' + QUOTENAME(@strPrefix + '_id') + ',
		c.' + QUOTENAME(@strPrefix + '_name') + '
FROM		mainAdmin AS a 
INNER JOIN	' + QUOTENAME(@strTableName) + ' AS c ON c.' + QUOTENAME(@strPrefix + '_id') + ' = a.parent_id
WHERE		a.expiryDate < ' + QUOTENAME(CONVERT(VARCHAR(30), @eDate, 121), '''') + '
		AND a.expiryDate <> ''1900-01-01''
		AND c.' + QUOTENAME(@strPrefix + '_intro') + ' IS NOT NULL
		AND a.visible = ''on''
ORDER BY	publishDate DESC'

PRINT	@SQL

E 12°55'05.63"
N 56°04'39.26"

ritudb
Starting Member

7 Posts

Posted - 2009-03-25 : 09:24:35

quote:

Originally posted by Peso

Works great for me

DECLARE	@SQL VARCHAR(1000),
	@strPrefix VARCHAR(200),
	@strTableName VARCHAR(200),
	@eDate DATETIME

SELECT	@strPrefix = 'Peso',
	@strTableName = 'SQLTeam',
	@eDate = GETDATE()

SET	@SQL = '
SELECT		c.' + QUOTENAME(@strPrefix + '_id') + ',
		c.' + QUOTENAME(@strPrefix + '_name') + '
FROM		mainAdmin AS a 
INNER JOIN	' + QUOTENAME(@strTableName) + ' AS c ON c.' + QUOTENAME(@strPrefix + '_id') + ' = a.parent_id
WHERE		a.expiryDate < ' + QUOTENAME(CONVERT(VARCHAR(30), @eDate, 121), '''') + '
		AND a.expiryDate <> ''1900-01-01''
		AND c.' + QUOTENAME(@strPrefix + '_intro') + ' IS NOT NULL
		AND a.visible = ''on''
ORDER BY	publishDate DESC'

PRINT	@SQL

E 12°55'05.63"
N 56°04'39.26"

ALTER PROCEDURE [dbo].[usp_issuesissCurrentList]
@strPrefix VARCHAR(150),
@strTableName VARCHAR(150),
@eDate DATETIME

AS
BEGIN

/*
** Stops the message indicating the number of rows affected
** by a T-SQL statement from being returned as part
** of the results.
*/
SET NOCOUNT ON

/*
** Start the Try Block
*/
BEGIN TRY
DECLARE
@Sql VARCHAR(1000)

SET @Sql = 'SELECT c.' + QUOTENAME(@strPrefix + '_id') + ',c.' + QUOTENAME(@strPrefix + '_name') + '
FROM mainAdmin a INNER JOIN ' + QUOTENAME(@strTableName) + ' AS c ON c.' + QUOTENAME(@strPrefix + '_id') + ' = a.parent_id
WHERE (a.expiryDate >= ' + QUOTENAME(CONVERT(DATETIME, @eDate, 121), '''') + ' OR a.expiryDate = ''01/01/1900'') AND c.' + QUOTENAME(@strPrefix + '_intro') +' IS NOT NULL AND a.visible=''on''
ORDER BY publishDate DESC'

Print @Sql

END TRY

when I say EXEC usp_issuesissCurrentList 'issue','mainIssues','25 March 2009' i GET THE QUERY AS O/P.
i COPIED IT N RAN IT N GOT PERFECT RESULTS

BUT WHEN i CHANGE PRINT TO Exec @Sql in Procedure n then
Exec usp_issuesissCurrentList 'issue','mainIssues','25 March 2009'

then
Msg 50000, Level 16, State 1, Procedure usp_issuesissCurrentList, Line 67
The name 'SELECT c.[issue_id],c.[issue_name]
FROM mainAdmin a INNER JOIN [mainIssues] AS c ON c.[issue_id] = a.parent_id
WHERE (a.expiryDate >= 'Mar 25 2009 12:00AM' OR a.expiryDate = '01/01/1900') AND c.[issue_intro] IS NOT NULL AND a.visible='on'
ORDER BY publishDate DESC' is not a valid identifier.

ritudb
Starting Member

7 Posts

Posted - 2009-03-25 : 09:28:04

I even copied the sql between quotes of error again
Msg 50000, Level 16, State 1, Procedure usp_issuesissCurrentList, Line 67
The name 'SELECT c.[issue_id],c.[issue_name]
FROM mainAdmin a INNER JOIN [mainIssues] AS c ON c.[issue_id] = a.parent_id
WHERE (a.expiryDate >= 'Mar 25 2009 1:27PM' OR a.expiryDate = '01/01/1900') AND c.[issue_intro] IS NOT NULL AND a.visible='on'
ORDER BY publishDate DESC' is not a valid identifier.

and run it and got perfect results.... am at wits end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-25 : 09:42:38

Exec ( @Sql )

E 12°55'05.63"
N 56°04'39.26"

ritudb
Starting Member

7 Posts

Posted - 2009-03-25 : 09:49:32