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?

Improve company productivity with a Business Account.Sign Up

x
 
Leigh PurvisConnect With a Mentor Database 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Rey Obrero (Capricorn1)Connect With a Mentor 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.