Error: Syntax error converting datetime from character string.

[techipa]

Hi All,
I get the following error when I run the below query. Can anyone help me with this one.

ERROR:
Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.


QUERY:
declare @m_UID int,
@m_use_dates bit,
@m_site_id int ,
@m_from datetime ,
@m_to datetime ,
@m_ctrl varchar(255),
@m_order int ,
@m_job int ,
@m_shpd bit ,
@m_inv varchar( 50),
@m_stu_site varchar( 30),
@m_active_only bit

set @m_UID = 4135
set @m_use_dates = 1
set @m_site_id = 0
set @m_from = '1/17/2007'
set @m_to = '1/18/2007'
set @m_ctrl = ''
set @m_order = -1
set @m_job = -1
set @m_shpd = 0
set @m_inv = ''
set @m_stu_site = ''
set @m_active_only = 1

SET NOCOUNT ON
-- DECLARE @l_end varchar( 17), @l_start varchar( 17),
declare @l_select varchar(8000), @l_order varchar(1000),
@new_todate datetime


-- SELECT @l_start = CONVERT(varchar, @m_from, 112),
-- @l_end = CONVERT(varchar, @m_to, 112)
select @new_todate = dateadd(day,1,@m_to)


SELECT @l_select = 'SELECT DISTINCT o.CPPS_Site, s.CustNo, s.Protocol_Name,
REPLACE(STR(s.CPPS_Job, 5), '' '', ''0'') AS Job,
rder_Num, o.Control_Num, o.Status,
dbo.prGetSiteInitial(o.CPPS_Site) AS ''SiteInitial'',
c.custname, oa.Study_Site,
u.User_Name AS Project_Mgr,
u.Email AS PM_Email,
CONVERT(varchar, o.Entered_Date, 107) AS Entered_Date,
ISNULL(CONVERT(varchar, o.Shipped_Date, 107), '''') AS Shipped_Date,
dbo.prDistGetOrderStatusName (o.Status, dbo.prDistGetTrackCount(rder_Num, o.CPPS_Site, 1), dbo.prDistGetTrackCount(rder_Num, o.CPPS_Site, 2)) AS StatusName,
dbo.prDistGetOrderStatusLink (o.Status, dbo.prDistGetTrackCount(rder_Num, o.CPPS_Site, 1)) AS StatusLink,
dbo.prDistGetOrderStatusColor (o.Status, dbo.prDistGetTrackCount(rder_Num, o.CPPS_Site, 1), dbo.prDistGetTrackCount(rder_Num, o.CPPS_Site, 2)) AS StatusColor,
CONVERT(varchar, o.' + CASE (@m_shpd)
WHEN 0 THEN 'Entered'
ELSE 'Shipped'
END + '_Date, 112) AS Sort_Date
FROM abcweb.dbrders o (NOLOCK)
INNER JOIN abcweb.dbo.Studies s (NOLOCK) ON ((s.Study_ID = o.Study_ID) AND (s.CPPS_Site = o.CPPS_Site)' +
CASE WHEN (@m_active_only <> 0) THEN ' AND (s.Active = 1)' ELSE '' END +
CASE WHEN (@m_job >= 0) THEN ' AND (s.CPPS_Job = ' + CONVERT(varchar, @m_job) + ')' ELSE '' END +
CASE WHEN (@m_site_id >= 0) THEN ' AND (o.CPPS_Site = ' + CONVERT(varchar, @m_site_id) + ')' ELSE '' END +
')
INNER JOIN abcweb.dbrder_Addresses oa (NOLOCK) ON ((rder_Num = oa.Order_Num) AND (oa.Addr_Type = 0) AND (o.CPPS_Site = oa.CPPS_Site) ' +
CASE WHEN ((ISNULL(RTRIM(LTRIM(@m_inv)), '')) <> '') THEN ' AND (LOWER(oa.L_Name) LIKE ''%' + LTRIM(RTRIM(REPLACE(LOWER(@m_inv), '''', ''''''''))) + '%'')' ELSE '' END +
CASE WHEN ((ISNULL(RTRIM(LTRIM(@m_stu_site)), '')) <> '') THEN ' AND (LOWER(oa.Study_Site) LIKE ''%' + LTRIM(RTRIM(REPLACE(LOWER(@m_stu_site), '''', ''''''''))) + '%'')' ELSE '' END +
')
INNER JOIN abcweb.dbo.Customer c (NOLOCK) ON ((s.CPPS_Site = c.Site) AND (c.custno = s.CustNo))
INNER JOIN abcweb.dbo.Users u (NOLOCK) ON ((u.User_ID = s.Project_Mgr) AND (u.CPPS_Site = s.CPPS_Site))
INNER JOIN #Prots p (NOLOCK) ON ((p.SiteID = s.CPPS_Site) AND (p.CustomerID = s.CustNo) AND (p.Protocol = s.Protocol_Name) AND (p.JobID = s.CPPS_Job))
WHERE (1=1) ' +
CASE (@m_use_dates)
WHEN 1 THEN
CASE (@m_shpd)
WHEN 0 THEN ' AND o.Entered_Date >= ' + '''@m_from''' + ' AND ' + 'o.Entered_Date < ' + '''@new_todate'''
ELSE ' AND ((o.Shipped_Date IS NOT NULL) AND ' +
'o.Shipped_Date >= ' + '''@m_from''' + ' AND ' + 'o.Shipped_Date <' + '''@l_end''' + ')'
END
ELSE ''
END +
CASE WHEN (@m_order > 0) THEN ' AND (rder_Num = ' + CONVERT(varchar, @m_order) + ')' ELSE '' END +
CASE WHEN (@m_shpd <> 0) THEN ' AND (o.Status = 5)' ELSE '' END +
CASE WHEN ((ISNULL(LTRIM(RTRIM(@m_ctrl)), '')) <> '') THEN ' AND (o.Control_Num LIKE ''%' + @m_ctrl + '%'')' ELSE '' END,
@l_order = + 'ORDER BY Sort_Date DESC, s.CustNo, s.Protocol_Name, o.CPPS_Site, rder_Num'

CREATE
TABLE #Prots
(SiteID int NOT NULL ,
CustomerID varchar(12) NOT NULL ,
CustomerName varchar(70) NOT NULL ,
Protocol varchar(50) NOT NULL ,
JobID int NOT NULL ,
Active bit NOT NULL)

INSERT INTO #Prots EXEC dbo.puDistGetProtocolListForUser @m_UID, 3, @m_site_id, 1

SET NOCOUNT OFF

EXEC(@l_select + ' ' + @l_order)
/**/
RETURN

Thanks,
-TechiPA

 

[SQLDenis]

dates should always be in ISO format: YYYYMMDD

so instead of this '1/17/2007' you need this '20070117'

that way you don't have a problem with a date like this 5/6/2005 what is that May 6th or June 5th

The ISO format is the only fail-safe format that will always work no matter if you live in Europe, US, Bangladesh,Kuala Lumpur or Antartica



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

That's a lot to look through.

First, you'll need to answer a couple questions:

Do either of these 2 queries return any records?
[tt][blue]
Select *
FROM abcweb.dbrders
Where IsDate(EnteredDate) = 0

Select *
FROM abcweb.dbrders
Where IsDate(ShippedDate) = 0
[/blue][/tt]

IsDate will return 0 when there is an invalid date, so essentially these queries will search for invalid data.

Also, run this and post the results here.

[tt][blue]
Select Column_Name, Data_Type
From Information_Schema.Columns
Where Table_Name = 'Orders'
And Column_Name In ('EnteredDate','ShippedDate')
[/blue][/tt]

I may have missed a 'date' column or 2.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[techipa]

HI George,

Thanks for the reply.
Following are the results you asked for

The first Query does not return anything
The second query does return the records

I got the result from the third query
Entered_Date datetime
Shipped_Date datetime

Do you think I am messing up at the single quotes in the WHERE clause?

Thanks,
techiPA

 

[SQLDenis]

Run this Gem

SET DATEFORMAT YDM

declare @m_from        datetime    ,
        @m_to          datetime    
                                     
set    @m_from  = '1/17/2007'
set    @m_to   = '1/18/2007' 


SELECT @m_from,@m_to


Go

see what happens?
now run this

SET DATEFORMAT YMD

declare @m_from        datetime    ,
        @m_to          datetime    
                                     
set    @m_from  = '1/17/2007'
set    @m_to   = '1/18/2007' 


SELECT @m_from,@m_to

that worked right, so depending on your setting if you use a dateformat that is NOT YYYYMMDD stuff will hit the fan

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

I suggest you change...

EXEC(@l_select + ' ' + @l_order)

TO

Print @l_select + ' ' + @l_order

This is a temporary change. Instead of executing the query, it will display the query that would be run. Then, if you don't see anything obviously wrong and fix the problem yourself, post it here so we can have a look.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[techipa]

This simple SQL also gives the same error

ERROR: Syntax error converting datetime from character string.

declare @l_select varchar(300),
@m_from datetime, @new_todate datetime
set @l_select = ''
set @m_from = '02/1/2007'
set @new_todate = '02/3/2007'

select @l_select = 'select top 10 * from Orders where ' + 'o.Entered_Date >= ' + @m_from + ' AND ' + 'o.Entered_Date < ' + @new_todate
select @l_select

 

[gmmastros]

Ok. I seew what the problem is now.

Essentially, you are using Dynamic SQL. In doing so, you are building a string that you will eventually execute. The problem is that you want to include a date in the string, but you are not converting the date to a string. You also need to double the single-quotes that surround your dates. Using your most recent example....

select @l_select = 'select top 10 * from Orders where ' + 'o.Entered_Date >= [!]''[/!]' + [!]Convert(VarChar(20), @m_from, 126)[/!]  + '[!]''[/!] AND ' + 'o.Entered_Date < [!]''[/!]' + [!]Convert(VarChar(20), @new_todate, 126) + ''''[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

you can't do it like that you have to use this

declare @l_select varchar(300),
@m_from datetime, @new_todate datetime
set @l_select = ''
set @m_from = '02/1/2007'
set @new_todate = '02/3/2007'

select @l_select = 'select top 10 * from Orders where ' + 'o.Entered_Date >= ''' + convert(varchar(20),@m_from) + ''' AND ' + 'o.Entered_Date < ''' + convert(varchar(20),@new_todate) +''''
select @l_select

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[techipa]

Hi George,

I can not use CONVERT in the WHERE clause. If I use the function it does not uses index on that column (entered_date). The performance is really bad in that case.
In the actual query the function was there. But then the execution plan shows this query very expensive and the reason is it does not uses index on the entered_date col.

My purpose is to remove the CONVERT function which will make the query much faster and will use the index.

Let me know if you have better solution.

Thanks,
techiPA

 

[gmmastros]

You are absolutely right about converts in the where clause. It is bad for performance.

Think about the process here.

1. You build a query.
2. You execute the query.

Building the query will be fast. It's in this process that the convert would appear. The query that is executed will not have any converts. I urge you to try this.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

>>I can not use CONVERT in the WHERE clause. If I use the function it does not uses index on that column (entered_date). The performance is really bad in that case.



????? first of all the convert happens before the string has been built, The SQL engine does not execute the convert when doing the select statement

Another question is: why do you use dynamic SQL, what is the reason?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[techipa]

George,

This is the stored procedure which builts and executes the dynamic SQL but I can not drop and re-create the proc so for time being I used the declare and set statements
instead of CREATE PROCEDURE [dbo].[AYZ] @m_UID int,
@m_use_dates bit,
@m_site_id int = -1,
@m_from datetime = 'Jan 1 1990',
@m_to datetime = 'Jan 1 2100',
@m_ctrl varchar(255) = '',
@m_order int = -1,
@m_job int = -1,
@m_shpd bit = 0,
@m_inv varchar( 50) = '',
@m_stu_site varchar( 30) = '',
@m_active_only bit = 1,
@m_prot_where text

I do not know why my company used the dynamic SQL.

This is the actual dynamic SQL from SQL profiler:
If I remove the CONVERT function from WHERE clause it is 56% faster. That shows the index is not been used due to the CONVERT function is used.

SELECT DISTINCT o.CPPS_Site, s.CustNo, s.Protocol_Name,
REPLACE(STR(s.CPPS_Job, 5), ' ', '0') AS Job,
rder_Num, o.Control_Num, o.Status,
dbo.prGetSiteInitial(o.CPPS_Site) AS 'SiteInitial',
c.custname, oa.Study_Site,
u.User_Name AS Project_Mgr,
u.Email AS PM_Email,
CONVERT(varchar, o.Entered_Date, 107) AS Entered_Date,
ISNULL(CONVERT(varchar, o.Shipped_Date, 107), '') AS Shipped_Date,
dbo.prDistGetOrderStatusName (o.Status, dbo.prDistGetTrackCount(rder_Num, o.CPPS_Site, 1), dbo.prDistGetTrackCount(rder_Num, o.CPPS_Site, 2)) AS StatusName,
dbo.prDistGetOrderStatusLink (o.Status, dbo.prDistGetTrackCount(rder_Num, o.CPPS_Site, 1)) AS StatusLink,
dbo.prDistGetOrderStatusColor (o.Status, dbo.prDistGetTrackCount(rder_Num, o.CPPS_Site, 1), dbo.prDistGetTrackCount(rder_Num, o.CPPS_Site, 2)) AS StatusColor,
CONVERT(varchar, o.Entered_Date, 112) AS Sort_Date
FROM CDSweb.dbrders o (NOLOCK)
INNER JOIN CDSweb.dbo.Studies s (NOLOCK) ON ((s.Study_ID = o.Study_ID) AND (s.CPPS_Site = o.CPPS_Site) AND (s.Active = 1) AND (o.CPPS_Site = 0))
INNER JOIN CDSweb.dbrder_Addresses oa (NOLOCK) ON ((rder_Num = oa.Order_Num) AND (oa.Addr_Type = 0) AND (o.CPPS_Site = oa.CPPS_Site) )
INNER JOIN CDSweb.dbo.Customer c (NOLOCK) ON ((s.CPPS_Site = c.Site) AND (c.custno = s.CustNo))
INNER JOIN CDSweb.dbo.Users u (NOLOCK) ON ((u.User_ID = s.Project_Mgr) AND (u.CPPS_Site = s.CPPS_Site))
INNER JOIN #Prots p (NOLOCK) ON ((p.SiteID = s.CPPS_Site) AND (p.CustomerID = s.CustNo) AND (p.Protocol = s.Protocol_Name) AND (p.JobID = s.CPPS_Job))
WHERE (1=1) AND (CONVERT(varchar, o.Entered_Date, 112) >= '02/08/2007') AND (CONVERT(varchar, o.Entered_Date, 112) < '02/12/2007') ORDER BY Sort_Date DESC, s.CustNo, s.Protocol_Name, o.CPPS_Site, rder_Num

George, please let me know if you think of any other way.

Thanks,
techiPA

 

[SQLDenis]

change

AND (CONVERT(varchar, o.Entered_Date, 112) >= '02/08/2007')
AND (CONVERT(varchar, o.Entered_Date, 112) < '02/12/2007')


to

AND o.Entered_Date >= '02/08/2007'
AND o.Entered_Date < '02/12/2007'

you need the convert to build the string, when you do a print the convert will not show up

example

declare @l_select varchar(300), 
    @m_from datetime, @new_todate datetime
set @l_select = ''
set @m_from = '02/1/2007'
set @new_todate = '02/3/2007'

select @l_select = 'select top 10 * from Orders where ' + 'o.Entered_Date >= ''' +  convert(varchar(20),@m_from)  + ''' AND ' + 'o.Entered_Date < ''' +  convert(varchar(20),@new_todate) +''''
print @l_select

see no converts in the printed output

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

I don't know how to make this any clearer (but I'll try).

declare @l_select varchar(300), 
    @m_from datetime, @new_todate datetime
set @l_select = ''
set @m_from = '02/1/2007'
set @new_todate = '02/3/2007'


select @l_select = 'select top 10 * from Orders where ' + 'o.Entered_Date >= ''' + Convert(VarChar(20), @m_from, 126)  + ''' AND ' + 'o.Entered_Date < ''' + Convert(VarChar(20), @new_todate, 126) + ''''

select @l_select

Run this query. Notice that the output window doesn't return data. It returns another query. ALL of the code (in the window above) simply performs converts and string operations (concatenation). Furthermore, this string is relatively small. If you tried to test the amount of time it takes to build the string, it would probably be un-measurable (because it is soo fast).

Notice, also, that the query that shows in the output window doesn't contain a single convert. Not 1. Not anywhere. If you run the query that appears in the output window of query analyzer, you will NOT get a table scan because the where clause (on this query) does not use a convert.

It's this query that appears in the output window that you need to optimize to be fast.

Does this make sense? I hope so.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[techipa]

Thanks George,

I tried the same and it worked. Thanks a lot for all your help and prompt responses

WHEN 0 THEN ' AND o.Entered_Date >= ' + '''' + cast ( @m_from as varchar(40)) + '''' + ' AND ' + 'o.Entered_Date < ' + '''' + cast(@new_todate as varchar(40)) + ''''
ELSE ' AND ((o.Shipped_Date IS NOT NULL) AND ' +
'o.Shipped_Date >= ' + '''' + cast( @m_from as varchar(40)) + '''' + ' AND ' + 'o.Shipped_Date <' + '''' + cast(@new_todate as varchar(40)) + '''' + ')'
END

Thanks again,

-techiPA

 

[AlexCuse]

techiPA, welcome to tek tips.

I suggest you take a look at this: faq183-874

I also suggest that you try the link labeled 'Process TGML' below where you type your post. This will tell you a lot about formatting, including these very important tags:

[ignore]

 typing in some code

[/ignore]

using these tags will give you a result like this in your post:

 typing in some code

Using these tags will help you to format your code and make it more readable (which is especially important when posting long queries like the one you posted here).

If you soak in all this information, you will become much better at posting your questions, which will help you to get more timely answers.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom