Link to home
Start Free TrialLog in
Avatar of jrharton
jrhartonFlag for United States of America

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Aaron Shilo
Aaron Shilo
Flag of Israel image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
disregard this line : CONVERT(nvarchar(30), GETDATE(), 126)
Avatar of jrharton

ASKER

Thank you!  works perfectly...