-2

I am getting a "Unable to Convert Date/Time from Character String" error message when I run my Access query, but I am not sure why. I have used this method before and have not had any trouble. 

SET NOCOUNT ON

DECLARE @StartDate date = '[Start date]', @EndDate date = '[End date]'

SELECT

CAST (Date as Date) as LocalDay
,SalesID 
,Status
,Wait
,PO_Number



FROM
cpo_test.dbo.table_agent_detail_view 

WHERE Date BETWEEN @StartDate And @EndDate
AND SOURCEID=1

Why is Cast not working in this instance?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Jenna Terral
  • 67
  • 2
  • 11
  • https://stackoverflow.com/questions/14119133/conversion-failed-when-converting-date-and-or-time-from-character-string-while-i – Christopher Lake Oct 12 '18 at 18:29
  • I don't use Access, but SQL has an IsDate() function. One (or more) of the values in your database does not contain a valid date string. Try simply doing a SELECT * FROM WHERE isDate(Date) =0
     – Sparky Oct 12 '18 at 19:01

2 Answers2

1

The problem is not with the CAST, it's with

DECLARE @StartDate date = '[Start date]', @EndDate date = '[End date]'

I get the same error in SSMS if I try to execute that statement alone. The problem goes away if I change it to

DECLARE @StartDate date = '2018-01-01', @EndDate date = '2018-12-31'

It's not clear what you are trying to accomplish by assigning the string value '[Start date]' to a date variable, but you're going to have to figure out another way to do it.

Note: You call it your "Access query" but it is not Access SQL, it's T-SQL (SQL Server) so I assume that you're running it as a pass-through query in Access. If so, then be aware that pass-through queries behave quite differently from regular Access queries because they are not processed by the Access expression evaluator or the Access Database Engine, they are passed directly to the ODBC data source (hence the name "pass-through query").

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
0

I think you are missing a cast in Date near where condition on your query. Try SELECT

CAST (Date as Date) as LocalDay ,SalesID ,Status ,Wait ,PO_Number

FROM cpo_test.dbo.table_agent_detail_view

WHERE CAST (Date as Date) BETWEEN @StartDate And @EndDate AND SOURCEID=1

Deepthi Shetty
  • 1