SQL
|
| Posted: 07/14/2004, 2:12 PM |
|
Syntax error converting datetime from character string
SQL, CCS
any help, here is a Example of my Code
Select Customer_Name,SCAC, COUNT(PRONUM) as 'Total Pros',
SUM(AMOUNT) as 'Total Amount',
SUM([COST SAVE]) as 'Total Cost Savings'
FROM ProDetail
WHERE [Pro Date] = '{PRODATE}'
GROUP BY Customer_Name,SCAC
|
SQL
|
| Posted: 07/15/2004, 6:05 AM |
|
I am trying to search between date, the field is called Pro Date, So on the search page i setup the following code
WHERE ( [PRO DATE] >= '{PRODATE}'
AND [PRO DATE] <= '{PRODATE1}' )
SQL Parameter for PRODATE
Variable Name PRODATE
Variable Type Date
Variable Format blank
Parameter Source PRODATE
Parameter Type URL
Parameter Format blank
Default Value 6/15/2004
SQL Parameter for PRODATE1
Variable Name PRODATE
Variable Type Date
Variable Format blank
Parameter SourcePRODATE
Parameter Type URL
Parameter Format blank
Default Value 6/15/2004
Also sometimes i get to following error
Error description: The value in field urlPRODATE is not valid.
Error description: The value in field urlPRODATE1 is not valid.
CCFormatDate function. Type mismatch. error '800a0fa0'
Unknown runtime error
/FreightManagement/Common.asp, line 1140
I ref. the line 1140
'CCFormatDate @0-3DB0C52B
Function CCFormatDate(DateToFormat, FormatMask)
Dim ResultArray(), I, Result
If VarType(DateToFormat) = vbEmpty Then
Result = Empty
ElseIf VarType(DateToFormat) <> vbDate Then
Err.Raise 4000, "CCFormatDate function. Type mismatch."
ElseIf IsEmpty(FormatMask) Then
Result = CStr(DateToFormat)
Else
ReDim ResultArray(UBound(FormatMask))
For I = 0 To UBound(FormatMask)
Select Case FormatMask(I)
Case "d" ResultArray(I) = Day(DateToFormat)
Case "w" ResultArray(I) = Weekday(DateToFormat)
Case "m" ResultArray(I) = Month(DateToFormat)
Case "q" ResultArray(I) = (Month(DateToFormat) + 3) \ 4
Case "y" ResultArray(I) = (DateDiff("d", "1/1" & "/" & Year(DateToFormat), Month(DateToFormat) & "/" & Day(DateToFormat) & "/" & Year(DateToFormat)) + 1)
Case "h" ResultArray(I) = CC12Hour(Hour(DateToFormat))
Case "H" ResultArray(I) = Hour(DateToFormat)
Case "n" ResultArray(I) = Minute(DateToFormat)
Case "s" ResultArray(I) = Second(DateToFormat)
Case "dd" ResultArray(I) = CCAddZero(Day(DateToFormat), 2)
Case "ww" ResultArray(I) = (DateDiff("ww", "1/1" & "/" & Year(DateToFormat), Month(DateToFormat) & "/" & Day(DateToFormat) & "/" & Year(DateToFormat)) + 1)
Case "mm" ResultArray(I) = CCAddZero(Month(DateToFormat), 2)
Case "yy" ResultArray(I) = Right(Year(DateToFormat), 2)
Case "hh" ResultArray(I) = CCAddZero(CC12Hour(Hour(DateToFormat)), 2)
Case "HH" ResultArray(I) = CCAddZero(Hour(DateToFormat), 2)
Case "nn" ResultArray(I) = CCAddZero(Minute(DateToFormat), 2)
Case "ss" ResultArray(I) = CCAddZero(Second(DateToFormat), 2)
Case "S" ResultArray(I) = "000"
Case "ddd" ResultArray(I) = CCSDateConstants.ShortWeekdays(Weekday(DateToFormat) - 1)
Case "mmm" ResultArray(I) = CCSDateConstants.ShortMonths(Month(DateToFormat) - 1)
Case "A/P" ResultArray(I) = CCGetAMPM(Hour(DateToFormat), "A", "P")
Case "a/p" ResultArray(I) = CCGetAMPM(Hour(DateToFormat), "a", "p")
Case "dddd" ResultArray(I) = CCSDateConstants.Weekdays(Weekday(DateToFormat) - 1)
Case "mmmm" ResultArray(I) = CCSDateConstants.Months(Month(DateToFormat) - 1)
Case "yyyy" ResultArray(I) = Year(DateToFormat)
Case "AM/PM" ResultArray(I) = CCGetAMPM(Hour(DateToFormat), "AM", "PM")
Case "am/pm" ResultArray(I) = CCGetAMPM(Hour(DateToFormat), "am", "pm")
Case "LongDate" ResultArray(I) = FormatDateTime(DateToFormat, vbLongDate)
Case "LongTime" ResultArray(I) = FormatDateTime(DateToFormat, vbLongTime)
Case "ShortDate" ResultArray(I) = FormatDateTime(DateToFormat, vbShortDate)
Case "ShortTime" ResultArray(I) = FormatDateTime(DateToFormat, vbShortTime)
Case "GeneralDate" ResultArray(I) = FormatDateTime(DateToFormat, vbGeneralDate)
Case Else
If Left(FormatMask(I), 1) = "\" Then _
ResultArray(I) = Mid(FormatMask(I), 1) _
Else
ResultArray(I) = FormatMask(I)
End Select
Next
Result = Join(ResultArray, "")
End If
CCFormatDate = Result
End Function
'End CCFormatDate
|
