Resolved: Oracle Date/Time as a Parameter in MSQuery

[Tuner]

I am attempting to use MSQuery to extract records from an Oracle transaction table by passing user defined Date/Time field parameters.

So if the date range for the query is for Fiscal Period 10 of this year, my parameters would be:
[StartDate] = #2002/10/1#
[EndDate] = #2002/11/1#

Daily transaction activity is in the early morning hours ending at 6:00AM so my Criteria is:
Between [StartDate] + .25 and [EndDate] + .25
Note: The +.25 represents 1/4day = 6hrs (6:00AM)

If I use the hardcoded Start/End Dates, my query works fine, but if I try and pass the dates as parameters I get "ORA-00932 inconsistent datatypes" error. And I can't seem to pass the Date/Time combination successfully....

If anyone has encountered a solution for how to pass a Date/Time Paremeter into MSQuery I would love to hear how you did it...
HELLLP, I'm starting to pull my few remaining hairs out!

Tony
This message was edited by Tuner on 2002-11-07 12:23
This message was edited by Tuner on 2002-11-07 12:28
This message was edited by Tuner on 2002-11-12 12:51

 

[lenze]

Hi Tuner:

I also had a difficult time using cell values for parameters when dates were involved. Thanks to Mark W assistance, I was able to work it out. Say A1 contains your start date. Then in another cell(can be hidden or on another sheet), enter =TEXT(A1,"yyyy-mm-dd"). Now use this cell as your parameter for start date. Change formatting argument in formula as needed. This worked for me. Good luck

 

[Tuner]

Thanks Lenz,
I had followed that thread of conversation and Mark's and your suggestions were partially helpfull.. The problem is that I need to pass a Date/Time and the format would look like this:

TEXT(A1,"yyyy-mm-dddd hh:mm:ss")

When the query runs it errors with following message:

"ORA-01830 date format picture ends before converting entire input string"

It seems to be the space between the date and the time segment of the format string that's lousing thigs up. Any suggestions on how to encapsulate the whole thing in a way that is friendly to MSQuery ?

 

[Tuner]

Problem Solved:

In MSQuery I use the following parameter criteria value string:

Between to_date([Start: dd-Mon-yy {hh24:mm}],'dd-mon-yy hh24:mi:ss') And to_date([End: dd-Mon-yy {hh24:mm}],'dd-mon-yy hh24:mi:ss')

Typical entry when prompted would be: "01-Sep-02 06:00" without the quotes. Seconds are not needed and time portion is optional.

"to_date" is a built-in function that converts strings to date based on format provided. Its syntax is: to_date(string,'format').

In my example the string to be converted is my parameter input. Note: everything in between the [ ] brackets is simply the prompt message for the parameter that gets displayed when the Query is running.

Finally, in Excel I am using the following formula to feed that Query's parameter:

=TEXT(DATE(LEFT($D$6,4),RIGHT($D$6,2),1),"dd-mmm-yyyy")&" 06:00"

Where in my case D6 contains ie "200209" representing fiscal period 09 in 2002. The user selects this fiscal period from a combobox which launches the query and produces the report....

HTHS,

Tony with special thanks to Roy...
This message was edited by Tuner on 2002-11-12 12:51

 

[zzman]

lenze,
I had a similar issue. Thanks your suggestion worked perfectly.

 

[RenegadeRazor]

Oh my goodness. This is the exact fix I have been looking for. I have been struggling with this for two weeks. This is the first forum that has clearly understood the issue and provided a perfect fix to the issue regarding dual parameter query date formating.

THIS IS AWESOME!! Thank you very much for the replies and knowledge of this forum. You have made my work much easier for my process technicians.

Thanks again.

 

[drozeveld]

I myself and excited like crazy that there are people are there that may be able to help me. I struggling with something very similar. I'm hoping that since you first posted this in 2002 that you've learned so much that you're going to be able to help me in a jiffy.

This is the sequel statement that I have in Microsoft Query and it works fine:

SELECT SO.DEL_DOC_NUM, SO.SHIP_TO_ZIP_CD, Sum(SO_LN.UNIT_PRC*qty)
FROM SALES.SO SO, SALES.SO_LN SO_LN
WHERE SO_LN.DEL_DOC_NUM = SO.DEL_DOC_NUM AND ((SO.STAT_CD<>'V') AND (SO_LN.VOID_DT Is Null))
GROUP BY SO.DEL_DOC_NUM, SO.SHIP_TO_ZIP_CD, SO.PU_DEL, SO.PU_DEL_DT, SO.SHIP_TO_ZONE_CD, SO.ORD_TP_CD
HAVING (SO.PU_DEL='D') AND (SO.PU_DEL_DT Between {ts '2013-05-01 00:00:00'} And {ts '2013-05-31 00:00:00'}) AND (SO.SHIP_TO_ZONE_CD='HNO')<wbr style="font-size: 14px; font-family: 'Droid Serif', Georgia, 'Times New Roman', serif; line-height: 23px;"> AND (SO.ORD_TP_CD='SAL')

But as soon as I want the statement to allow me to enter data for the dates or the zone cd, I receive the error ORA-00936: missing expression.