asked on

Matching Date dd-mmm-yy

Here is my PERL SQL statement which is trying to match a date in an Access table:

select variable1 from mytable where table_date = '23-Jun-02';

This code is failing on the date match. But the date value is exactly what appears in the table?

-G

GreymanMSC

Does the date in the table include a time value? You may need to truncate table_date to select only the date part.

Leigh Purvis

Is your field a date type field?
The appearance in the field is then just to options/preferences and local settings.
You should be fine if you delimit the date value properly (Jet uses different syntax for this than ANSI standard)

elect variable1 from mytable where table_date = #23-Jun-02#

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Leigh Purvis

Of course if your field table_date is already a date type - then there's no value in doing that at all (it'll just slow your query down).

If it's text - it might help you get the right result.
(But it reeeally shouldn't be text ideally).

Rey Obrero (Capricorn1)

Leigh,
the query defaults the format of date to mm/dd/yyyy

GreymanMSC

IE: It eliminates any Time Values.

ASKER CERTIFIED SOLUTION

Leigh Purvis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gmanpert

ASKER

Ok, got it. This works:

select variable1 from mytable where Format(myfield,'dd-mmm-yy') = Format('23-Jun-02','dd-mmm-yy');

Where Format('23-Jun-02','dd-mmm-yy') is entered in PERL code as:

format(\'$date\',\'dd-mmm-yy\');

Thanks!
-g

Leigh Purvis

Is the field a date type field?

gmanpert

ASKER

Yes, the field was an Access Date field.

-g

Leigh Purvis

OK
Did you try the first suggestion of using the # delimiter?

What you've ended up doing is converting your date values into a string and comparing the strings.
Which isn't ideal generally speaking.

gmanpert

ASKER

No I did not try that as I was in a hurry. However, I will test your code when I get time as it looks to be potentially useful.

Thanks for the continued insight.

Best,

-G