Confusing Error: Conversion failed when converting datetime from character string

• nick.owens

  Grasshopper

  Points: 21

  More actions

  June 25, 2008 at 9:11 am

  #189613

  The following query worked fine in MS Access (of course the apostrophes were hashes "#").

  SELECT DISTINCT EA_ID, EA_ACC_ID, EA_LEA_ID, EA_DM_ID, EA_SCR_ID, EA_LD_ID, EA_LLT_ID, EA_LFTY_ID, EA_ADDRESS, (SELECT COUNT(ER_ID) FROM EMAIL_RETURN WHERE ER_STATUS = 'A' AND ER_ADDRESS = EA_ADDRESS AND ER_DATE_INSERTED >= '3/27/2008 10:43:04 AM'), (SELECT COUNT(ER_ID) FROM EMAIL_RETURN WHERE ER_STATUS = 'A' AND ER_ADDRESS = EA_ADDRESS AND ER_TYPE = 'H' AND ER_DATE_INSERTED >= '3/27/2008 10:43:04 AM'), EA_LDT_ID, EA_FET_ID FROM (EMAIL_ADDRESS EA INNER JOIN EMAIL_RETURN ER ON (EA.EA_ADDRESS = ER.ER_ADDRESS AND ER.ER_STATUS = 'A' AND ER.ER_DATE_INSERTED > '6/25/2008 10:36:09 AM')) WHERE EA_STATUS = 'A' AND EA_DELIVERABLE = 'Y' ORDER BY EA_ID ASC

  When running this query, I get the error "Conversion failed when converting datetime from character string.". The Date/Time values are formatted properly and the ER_DATE_INSERTED column is of type "datetime" and NOT NULL.

  What could be going on? What can I do to keep troubleshooting this?

• GSquared

  SSC Guru

  Points: 260824

  More actions

  June 25, 2008 at 11:50 am

  #834182

  I'd break out the sub-queries and test them as stand-alones.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• RubyRed

  Mr or Mrs. 500

  Points: 509

  More actions

  June 25, 2008 at 1:01 pm

  #834226

  Try this query.

  SELECT DISTINCT EA_ID, EA_ACC_ID, EA_LEA_ID, EA_DM_ID, EA_SCR_ID, EA_LD_ID, EA_LLT_ID, EA_LFTY_ID, EA_ADDRESS,

  (SELECT COUNT(ER_ID) FROM EMAIL_RETURN WHERE ER_STATUS = 'A' AND ER_ADDRESS = EA_ADDRESS

  AND CONVERT(VARCHAR(19),ER_DATE_INSERTED,120) >= '2008-03-27 10:43:04'),

  (SELECT COUNT(ER_ID) FROM EMAIL_RETURN WHERE ER_STATUS = 'A'

  AND ER_ADDRESS = EA_ADDRESS AND ER_TYPE = 'H'

  AND CONVERT(VARCHAR(19),ER_DATE_INSERTED,120) >= '2008-03-27 10:43:04'),

  EA_LDT_ID, EA_FET_ID

  FROM (EMAIL_ADDRESS EA INNER JOIN EMAIL_RETURN ER

  ON (EA.EA_ADDRESS = ER.ER_ADDRESS AND ER.ER_STATUS = 'A'

  AND CONVERT(VARCHAR(19),ER.ER_DATE_INSERTED,120) >= '2008-0625 10:36:09')) WHERE EA_STATUS = 'A' AND EA_DELIVERABLE = 'Y' ORDER BY EA_ID ASC