Error converting Date

Question

Post reply

Error converting Date

patrick.palmer

SSC Enthusiast

Points: 118
More actions
February 14, 2013 at 9:10 am

#274140

I am running the following and get this error "Conversion failed when converting date and/or time from character string."
I am looking for the date from Field DATEDESC minus 30 days. Can you help, please.
select
activityname,
dd.datedesc,
dateadd(hour, - 5, DD.DateDesc) AS date_completed,
getdate()-1 as Date_Completed,
MAX(AL.CECredits) AS CECredits,
CHARINDEX('- ', CER.CERegion_Name) + 2,
CHARINDEX(' -', CER.CERegion_Name),
CHARINDEX('- ', CER.CERegion_Name) + 1 - CHARINDEX('- ', CER.CERegion_Name) - 2 AS BoardName,
RIGHT(CER.CERegion_Name, CHARINDEX('-', REVERSE(CER.CERegion_Name)) - 1) AS License,
CASE RTRIM(LTRIM(V.VenName))
WHEN 'BayCare Education Services' THEN '50-201'
WHEN 'Morton Plant Mease Health Care' THEN '50-1378'
WHEN 'St. Josephs Hospital' THEN '50-1867'
END AS ProviderId,
CASE RTRIM(LTRIM(RegistrationStatusID))
WHEN '0' THEN 'Registered'
WHEN '1' THEN 'Cancelled'
WHEN '2' THEN 'On Wait List'
WHEN '3' THEN 'Holding for Approval'
WHEN '4' THEN 'Completed'
WHEN '5' THEN 'No Show'
WHEN '6' THEN 'Reserved Wait List'
WHEN '7' THEN 'Expired Wait List'
WHEN '8' THEN 'Registration Transfered'
WHEN '9' THEN 'Waived'
WHEN '10' THEN 'In Progress'
END as Status,
AL.ApprovalCode,
cct.CreditCatType_Name,
dimUser.EmpNo,
dimUser.EmpFullName1,
dimUser.PrimaryJobName,
dimUser.PrimaryOrgName,
DATEPART (yyyy, EmpStartDt) as Hire_Year, +
DATEPART (mm, EmpStartDt) as Hire_Month, +
DATEPART (dd, EmpStartDt) as Hire_DAY,
dimUser.EmpStartDt,
dimUser.OptEmp_Txt2,
RegistrationStatusID
From
factAttempt AS fact
JOIN dimDate AS DD ON DD.DateID = fact.EndDtID
JOIN dimActivity AS A ON A.ID = fact.ActivityID
JOIN TBL_TMX_ActLinks AS AL ON AL.ActivityFK = A.ActivityFK AND AL.LinkType = 3
JOIN dimUser ON dimUser.ID = fact.UserID
JOIN TBL_TMX_ActOpt AS AO ON AO.ActivityFK = A.ActivityFK
JOIN tblEmpCERegion AS ER ON ER.EmpCERegion_EmpFK = dimUser.EmpFK
JOIN CERegion AS CER ON CER.CERegion_PK = ER.EmpCERegion_CERegionFK
AND CER.CERegion_Name LIKE '%-%'
JOIN factResourceVen AS AV ON AV.ActivityID = A.ID
JOIN dimVen AS V ON V.ID = AV.VenID
left outer join TBL_TMX_ActCECC as CECC on CECC.ActivityFK = A.ID
left outer join CreditCatType as cct on cct.CreditCatType_PK = CECC.CreditCatTypeFK
where CER.CERegion_PK = 5
group by
activityname,
CER.CERegion_Name,
V.VenName,
AL.ApprovalCode,
cct.CreditCatType_Name,
dimUser.EmpNo,
dimUser.EmpFullName1,
dimUser.PrimaryJobName,
dimUser.PrimaryOrgName,
dimUser.EmpStartDt,
dimUser.OptEmp_Txt2,
dd.datedesc,
RegistrationStatusID
--select top 10 * from dbo.dimOrg
--select top 10 * from dbo.dimuser

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Abu Dina SSChampion Points: 14155 More actions · Answer 1

Run this:

SELECT *

FROM Dimdate

WHERE ISDATE(datedesc) = 0

If this comes back with anything then you'll know which ones SQL can't convert to datetime.

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

ScottPletcher SSC Guru Points: 101115 More actions · Answer 2

Do you want to test DateDesc for within the last 30 days?

If so, then maybe this (added that check to the WHERE clause):

select

activityname,

dd.datedesc,

dateadd(hour, - 5, DD.DateDesc) AS date_completed,

getdate()-1 as Date_Completed,

MAX(AL.CECredits) AS CECredits,

CHARINDEX('- ', CER.CERegion_Name) + 2,

CHARINDEX(' -', CER.CERegion_Name),

CHARINDEX('- ', CER.CERegion_Name) + 1 - CHARINDEX('- ', CER.CERegion_Name) - 2 AS BoardName,

RIGHT(CER.CERegion_Name, CHARINDEX('-', REVERSE(CER.CERegion_Name)) - 1) AS License,

CASE RTRIM(LTRIM(V.VenName))

WHEN 'BayCare Education Services' THEN '50-201'

WHEN 'Morton Plant Mease Health Care' THEN '50-1378'

WHEN 'St. Josephs Hospital' THEN '50-1867'

END AS ProviderId,

CASE RTRIM(LTRIM(RegistrationStatusID))

WHEN '0' THEN 'Registered'

WHEN '1' THEN 'Cancelled'

WHEN '2' THEN 'On Wait List'

WHEN '3' THEN 'Holding for Approval'

WHEN '4' THEN 'Completed'

WHEN '5' THEN 'No Show'

WHEN '6' THEN 'Reserved Wait List'

WHEN '7' THEN 'Expired Wait List'

WHEN '8' THEN 'Registration Transfered'

WHEN '9' THEN 'Waived'

WHEN '10' THEN 'In Progress'

END as Status,

AL.ApprovalCode,

cct.CreditCatType_Name,

dimUser.EmpNo,

dimUser.EmpFullName1,

dimUser.PrimaryJobName,

dimUser.PrimaryOrgName,

DATEPART (yyyy, EmpStartDt) as Hire_Year, +

DATEPART (mm, EmpStartDt) as Hire_Month, +

DATEPART (dd, EmpStartDt) as Hire_DAY,

dimUser.EmpStartDt,

dimUser.OptEmp_Txt2,

RegistrationStatusID

From

factAttempt AS fact

JOIN dimDate AS DD ON DD.DateID = fact.EndDtID

JOIN dimActivity AS A ON A.ID = fact.ActivityID

JOIN TBL_TMX_ActLinks AS AL ON AL.ActivityFK = A.ActivityFK AND AL.LinkType = 3

JOIN dimUser ON dimUser.ID = fact.UserID

JOIN TBL_TMX_ActOpt AS AO ON AO.ActivityFK = A.ActivityFK

JOIN tblEmpCERegion AS ER ON ER.EmpCERegion_EmpFK = dimUser.EmpFK

JOIN CERegion AS CER ON CER.CERegion_PK = ER.EmpCERegion_CERegionFK

AND CER.CERegion_Name LIKE '%-%'

JOIN factResourceVen AS AV ON AV.ActivityID = A.ID

JOIN dimVen AS V ON V.ID = AV.VenID

left outer join TBL_TMX_ActCECC as CECC on CECC.ActivityFK = A.ID

left outer join CreditCatType as cct on cct.CreditCatType_PK = CECC.CreditCatTypeFK

where CER.CERegion_PK = 5

and 1 =

case when when ISDATE(DD.DateDesc) = 0 then 0

when DD.DateDesc >= DATEADD(DAY, -30, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) then 1

else 0 end

group by