We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Matching Date dd-mmm-yy

gmanpert
gmanpert asked
on
Medium Priority
332 Views
Last Modified: 2012-08-13
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
Comment
Watch Question

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

Commented:
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#
CERTIFIED EXPERT
Top Expert 2016
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#

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
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).
CERTIFIED EXPERT
Top Expert 2016

Commented:
Leigh,
the query defaults the format of date  to mm/dd/yyyy
IE: It eliminates any Time Values.
Database Developer
CERTIFIED EXPERT
Commented:
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.

Author

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
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Is the field a date type field?

Author

Commented:
Yes, the field was an Access Date field.

-g
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
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.

Author

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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.