jrharton
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.
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_
Order By
a.DischargeDateTime Collate SQL_Latin1_General_CP1_CS_
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
disregard this line : CONVERT(nvarchar(30), GETDATE(), 126)
ASKER
Thank you! works perfectly...