Skip to main content
D
djkhalif
Inspiring
October 23, 2009
Question

The value '' cannot be converted to a number: Help anyone

  • October 23, 2009
  • 3 replies
  • 1675 views

Good morning all,

I have this query below, it works fine when I select a start date with a single digit day, ie. 9/7/2009. But any selected start date after the week containing 9/7/2009 I get this error:

The value '' cannot be converted to a number

SELECT     DATEADD(wk, DATEDIFF(wk, 0, DateProd), 0) AS Weekly_Production, sum(UnitsProd) AS Total_Units
FROM         tbl_Assembly_Production
WHERE dateProd Between '#FORM.dateFROM#' and '#FORM.dateTo#'
GROUP BY DATEADD(wk, DATEDIFF(wk, 0, DateProd), 0)
ORDER BY Weekly_Production

Has anyone seen this before? I've checked my table and all the dates seem good.

Thanks in advance,

djkhalif

This topic has been closed for replies.

3 replies

D
djkhalifAuthor
Inspiring
October 24, 2009

Dan,

I posted my code; would you mind looking at it?

D
Dan_Bracuk
Inspiring
October 25, 2009

Where did you post it?

D
djkhalifAuthor
Inspiring
October 25, 2009

Dan,

I thought it had uploaded but it didn't happen.

D
djkhalifAuthor
Inspiring
October 24, 2009

Dan,

I uploaded my coded; would you mind looking at it?

Thanks,

djkhalif

D
Dan_Bracuk
Inspiring
October 23, 2009

Chances are your query is not returning any records and the error occurs when you try to process it.  Either cfdump it or look at your debugging info to verify this.  Other comments:

This:

DATEADD(wk, DATEDIFF(wk, 0, DateProd), 0) AS Weekly_Production

is very strange.  Datediff returns a number and DateAdd returns a date.  When you say that it works fine under certain circumstances, it might be running error free but could be giving you an incorrect answer.

This:

WHERE dateProd Between '#FORM.dateFROM#' and '#FORM.dateTo#'
has two or three problems.  The first is that you did not convert the strings to date objects.  The second is that you are not using cfqueryparam.  The third is that, if the datatype of dateProd has a date and time, you may be missing records.  Better logic would be

where proddate >= date1

and proddate < the day after date 2

D
djkhalifAuthor
Inspiring
October 23, 2009

Dan,

Thanks. I will try that this weekend.

djkhalif

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices