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
gmanpertAsked:
Who is Participating?
 
Leigh PurvisDatabase DeveloperCommented:
Cap,
How do you mean?  "query defaults the format of date  to mm/dd/yyyy" ?

You mean the query you suggested?
A query results grid just shows dates in whatever the specified system preference is.

There's not much we can say until we find out from the questioner what the field's type really is.
But if it *is* date (as it should be) then formatting it makes no difference - as soon as you CDate is it's converted back to a date - and a date comparison is performed

e.g. for standard US settings (mm/dd/yyyy)
CDate(Format(Date,"yyyy-mm-dd")) = 10/27/2006
The formatting is irrelevant.
0
 
GreymanMSCCommented:
Does the date in the table include a time value?  You may need to truncate table_date to select only the date part.
0
 
Leigh PurvisDatabase DeveloperCommented:
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#
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Rey Obrero (Capricorn1)Commented:
if the field in the table is A Date/Time type

use

select variable1 from mytable where Cdate(format(table_date,'mm/dd/yyyy'))=#06/23/2002#
0
 
Leigh PurvisDatabase DeveloperCommented:
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).
0
 
Rey Obrero (Capricorn1)Commented:
Leigh,
the query defaults the format of date  to mm/dd/yyyy
0
 
GreymanMSCCommented:
IE: It eliminates any Time Values.
0
 
gmanpertAuthor Commented:
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
0
 
Leigh PurvisDatabase DeveloperCommented:
Is the field a date type field?
0
 
gmanpertAuthor Commented:
Yes, the field was an Access Date field.

-g
0
 
Leigh PurvisDatabase DeveloperCommented:
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.
0
 
gmanpertAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.