SQLTeam.com | Weblogs | Forums

Cannot Create Dynamic SQL datetime -- convertion failed


#1

Hi.
I get Conversion failed when converting date and/or time from character string.

declare 	@MailQuery as varchar(8000)
declare @date datetime
set @date= getdate()
set @MailQuery = '
USE [VISTAIT]
SET NOCOUNT ON 
set transaction isolation level read uncommitted 
 Declare @date datetime = ' + GETDATE()  + '
select count(tblOrderTicketHistory.OrderTH_intTicketId), OrderH_strCinemaId,OrderH_intVistaTransNumber,OrderH_intVistaBookingNumber,OrderH_dtmInitiated,OrderH_strEmail
from tblOrderHistory with(nolock)
left join tblOrderTicketHistory on tblOrderTicketHistory.OrderH_intID =  tblOrderHistory.OrderH_intID
 where OrderH_strEmail like ''%mailinator%'' and OrderH_strCardLastFour = ''2274''
and DATEDIFF(hour, OrderH_dtmInitiated, ''' + convert(varchar(20), GETDATE(), 120) + ''') >=2 and DATEDIFF(hour, OrderH_dtmInitiated, ''' + convert(varchar(20), GETDATE(), 120) + ''') <=3
group by OrderH_strCinemaId,OrderH_intVistaTransNumber,OrderH_intVistaBookingNumber,OrderH_dtmInitiated,OrderH_strEmail
'

#2

Change the 8th line in your query which reads:

Declare @date datetime = ' + GETDATE() + '

to this

Declare @date datetime = ''' + CONVERT(VARCHAR(20), GETDATE(), 120)  + '''

#3

Thanks. Can't believe I googled 100 threads and did not find anything!


#4

Although I must be missing some quotation mark as it does notwork.


#5

Got it!


declare 	@MailQuery as varchar(8000)
declare @date datetime
set @date= getdate()
set @MailQuery = '
USE [VISTAIT]
SET NOCOUNT ON 
set transaction isolation level read uncommitted 
 Declare @date datetime =  ''' + CONVERT(VARCHAR(20), GETDATE(), 120)  + '''
select count(tblOrderTicketHistory.OrderTH_intTicketId), OrderH_strCinemaId,OrderH_intVistaTransNumber,OrderH_intVistaBookingNumber,OrderH_dtmInitiated,OrderH_strEmail
from tblOrderHistory with(nolock)
left join tblOrderTicketHistory on tblOrderTicketHistory.OrderH_intID =  tblOrderHistory.OrderH_intID
 where OrderH_strEmail like ''%mailinator%'' and OrderH_strCardLastFour = ''2274''
and DATEDIFF(hour, OrderH_dtmInitiated, ''' + convert(varchar(20), GETDATE(), 120) + ''') >=2 and DATEDIFF(hour, OrderH_dtmInitiated, ''' + convert(varchar(20), GETDATE(), 120) + ''') <=3
group by OrderH_strCinemaId,OrderH_intVistaTransNumber,OrderH_intVistaBookingNumber,OrderH_dtmInitiated,OrderH_strEmail
'

#6

I reckon it would be pretty hard to to Google for that - unless you already knew what the cause is ... in which case you'd also already know the answer! ... Easier to just Ask JamesK !!

You sure you want to do that? You will, occasionally, get some rows missing altogether and some rows included twice. If the accuracy of the query is not important its fine of course.

Those won't be SARGable, and thus won't use any indexes even if available, so the query may be slow, and will scale badly as the database grows.

I'm not sure I've properly got my head around what is trying to be achieved here, but I think this is the equivalent code (and it is SARGable)

AND OrderH_dtmInitiated >= DATEADD(Hour, -3, GetDate())
AND OrderH_dtmInitiated <= DATEADD(Hour, -2, GetDate())

I suppose it slightly raises the question why are you getting the GetDate() date/time into @date and then passing that into the @MailQuery string (with the resultant datatype conversion / concatenation etc.) rather than just referring to GetDate() directly in the SQL string?

set @MailQuery = '
USE [VISTAIT]
SET NOCOUNT ON 
set transaction isolation level read uncommitted 

-- UNUSED Declare @date datetime = GETDATE() 

select count(tblOrderTicketHistory.OrderTH_intTicketId), OrderH_strCinemaId,OrderH_intVistaTransNumber,OrderH_intVistaBookingNumber,OrderH_dtmInitiated,OrderH_strEmail
from tblOrderHistory with(nolock)
left join tblOrderTicketHistory on tblOrderTicketHistory.OrderH_intID =  tblOrderHistory.OrderH_intID
 where OrderH_strEmail like ''%mailinator%'' and OrderH_strCardLastFour = ''2274''

and DATEDIFF(hour, OrderH_dtmInitiated, GETDATE()) >=2 
and DATEDIFF(hour, OrderH_dtmInitiated, GETDATE()) <=3

group by OrderH_strCinemaId,OrderH_intVistaTransNumber,OrderH_intVistaBookingNumber,OrderH_dtmInitiated,OrderH_strEmail
'

but that would still not solve the SARGable issue.


#7

Hi.
I did not write the whole thing as it is actually sending an SQL email.
So I'm using a non dynamic Sproc with the @date variable outside and if I have rows, I use the Dynamic SQL.
The @date was left unintentionally in this code.

Hi. I would change the non sargable thanks. I was trying to get the dynamic SQL to work with everything I could think of as conversions and James code worked so I left it.
Will try to implement the sargable tomorrow.
thanks


#8

I also do not mind read uncommitted here.
It's not crucial data.