Oracle Date Format 2

[mdlaugh1]

Trying to construct a query using Query Grid to select records with ACTUAL_FINISH_DATE >= 1/29/07 and this is my results list. Access 2000 and Oracle 9i backend.

My expression in Grid column is

Format([SYNERGEN_SA_WORK_ORDER_TASK]![ACTUAL_FINISH_DATE],"yyyy-mm-dd")

In the Criteria row I have tried >= '01/29/07'
>= "01/29/2007", "2007/01/29"

and I have tried rearranging the Format statement to be "mm-dd-yyyy".

I either pull no records or i pull something like the following list -- more records than >= 01/29/07.

What am I missing?
THANK YOU!


ACTUAL_FINISH_DATE WO_NO TYPE_OF_WORK

1/31/2007 3:30:00 PM 0701244 *ADMIN
1/31/2007 2:00:00 PM 0701265 *ADMIN
1/31/2007 9:00:52 AM 0701136 *ADMIN
1/30/2007 4:00:00 PM 0701223 *ADMIN
1/30/2007 12:30:00 PM 0701243 *ADMIN
1/30/2007 10:00:00 AM 0701243 *ADMIN
1/30/2007 8:56:37 AM 0701135 *ADMIN
1/29/2007 4:00:00 PM 0701217 *ADMIN
1/26/2007 4:00:00 PM 0701214 *ADMIN
1/26/2007 11:08:56 AM 0701116 *ADMIN
1/26/2007 11:00:00 AM 0700949 *ADMIN
1/25/2007 12:30:00 PM 0700941 *ADMIN
1/25/2007 9:25:39 AM 0701115 *ADMIN
1/19/2007 4:00:00 PM 0701104 *ADMIN
1/18/2007 3:00:00 PM 0700413 *ADMIN
1/18/2007 12:29:18 PM 0700538 *ADMIN
1/10/2007 10:30:00 AM 0700534 *ADMIN
1/9/2007 4:00:00 PM 0701096 *ADMIN
1/9/2007 10:30:00 AM 0700386 *ADMIN
1/9/2007 7:52:00 AM 0700537 *ADMIN
1/8/2007 4:00:00 PM 0700418 *ADMIN
1/8/2007 4:00:00 PM 0701095 *ADMIN
1/8/2007 4:00:00 PM 0700406 *ADMIN
1/8/2007 3:29:11 PM 0700536 *ADMIN

 

[PHV]

What about this ?
Your expression in Grid column is:
Format([SYNERGEN_SA_WORK_ORDER_TASK]![ACTUAL_FINISH_DATE],"yyyy-mm-dd")

In the Criteria cell: >="2007-01-29"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mdlaugh1]

oh!! it was using the "/" that caused the problem, when I changed it to "-" it works.

I want the final query to use a parameter prompt...

I'd like the user to be able to enter the date prompts as Beg date 01/01/2007 and End date 01/31/2007.

Do I need to change the Format statement in the Expression Builder?

And can they enter a / or are they restricted to using the - when entering the date prompt?

 

[PHV]

In the Criteria cell: >=Format([enter the date], "yyyy-mm-dd")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mdlaugh1]

Got it!

ok, one last question (I hope) on this subject.

I've tried to find rationale for the format()"yyyy-mm-dd" as opposed to format ()"mm-dd-yyyy" but no success, although changing my format statement to the "mm-dd-yyyy" definitely does not work... I pull data from 2006.

My actual data is in form of mm/dd/yyyy as shown in the listing above, and when I enter the date at the prompt I enter it as 01/15/07 and as long as I use your statement of format ()"yyyy-mm-dd" it works. Just trying to understand the relationship and why "mm-dd-yyyy" format does not work.

thanks!!!

 

[PHV]

Just trying to understand
You don't compare dates but text strings.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mdlaugh1]

ok, but why does the following not work?

Format([SYNERGEN_SA_WORK_ORDER_TASK]![ACTUAL_FINISH_DATE],"mm-dd-yyyy")

with

Criteria: >=Format([enter the date], "mm-dd-yyyy")

when the date field contains:
1/31/2007 3:30:00 PM

but setting it up as "yyyy-mm-dd" and then entering at the prompt as 01/15/07 does work?

If there's a good "Help" on this please feel free to point me there.

thank you - I appreciate your time!!!

 

[cmmrfrds]

The format yyyy-mm-dd is an ISO standard format recognized by all database vendors. The Oracle standard date format on your system is set by the DBA and can vary on each Oracle system depending on the DBA. Check with your DBA on your Oracle database. If the DBA did not set the format it probably defaults to dd-mon-yyyy. You probably would not run into this on Sql Server since the default is usually mm/dd/yyyy.