Conversion failed when converting date and/or time from character string

Hey- I am yet to figure out the syntax for the date issue with collation.

declare @beginDate datetime, @endDate datetime
set @beginDate = '04/01/2011' ---This is always a rolling 2-years.
set @endDate = '05/01/2011'
Select
a.DischargeDateTime,
a.AbstractID,
a.VisitID,
a.AccountNumber
from dbo.AbstractData as a
where a.DischargeDateTime Collate SQL_Latin1_General_CP1_CS_AS  >= '@beginDate'  and a.DischargeDateTime Collate SQL_Latin1_General_CP1_CS_AS  < '@beginDate'
Order By
a.DischargeDateTime Collate SQL_Latin1_General_CP1_CS_AS

I have tried MANY different statements nothing has worked.  This errors as ‘Msg 447, Level 16, State 0, Line 4
Expression type datetime is invalid for COLLATE clause.’

Original Statement:
/****  More testing   ****/
Drop table ##AbstractData
declare @beginDate datetime, @endDate datetime
set @beginDate = '04/01/2009'
set @endDate = '05/01/2011'
Select
a.DischargeDateTime,
a.AbstractID,
a.VisitID,
a.AccountNumber
into ##AbstractData
from dbo.AbstractData as a
where (a.DischargeDateTime  >= '@beginDate'  and a.DischargeDateTime < '@endDate')
Order By a.VisitID

Without a collation statement I get the error of ‘Conversion failed when converting date and/or time from character string.’

What did you do?  This is driving me crazy.  Sigh.  And yes- I have converted and cast’d with still no luck!  The data will return when I convert, but it is not filtering.  Lot’s of documentation on this- but, nothing with a date example like this.
jrhartonAsked:
Who is Participating?
 
Aaron ShiloConnect With a Mentor Chief Database ArchitectCommented:
try this

Drop table ##AbstractData
declare @beginDate datetime, @endDate datetime
set @beginDate = '04/01/2009' 
set @endDate = '05/01/2011'
Select 
a.DischargeDateTime,
a.AbstractID,
a.VisitID,
a.AccountNumber
into ##AbstractData
from dbo.AbstractData as a 
where (CONVERT(nvarchar(30), a.DischargeDateTime  , 101)>=@beginDate 
 and CONVERT(nvarchar(30), a.DischargeDateTime , 101)< @endDate)
Order By a.VisitID

CONVERT(nvarchar(30), GETDATE(), 126)

Open in new window

0
 
Aaron ShiloChief Database ArchitectCommented:
disregard this line : CONVERT(nvarchar(30), GETDATE(), 126)
0
 
jrhartonAuthor Commented:
Thank you!  works perfectly...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.