?
Solved

Matching Date dd-mmm-yy

Posted on 2006-10-26
12
Medium Priority
?
318 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
0
Comment
Question by:gmanpert
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 16

Expert Comment

by:GreymanMSC
ID: 17816015
Does the date in the table include a time value?  You may need to truncate table_date to select only the date part.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17816026
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1200 total points
ID: 17816031
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17816041
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17816071
Leigh,
the query defaults the format of date  to mm/dd/yyyy
0
 
LVL 16

Expert Comment

by:GreymanMSC
ID: 17816080
IE: It eliminates any Time Values.
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 300 total points
ID: 17816111
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
 

Author Comment

by:gmanpert
ID: 17816200
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17816206
Is the field a date type field?
0
 

Author Comment

by:gmanpert
ID: 17830048
Yes, the field was an Access Date field.

-g
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17830145
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
 

Author Comment

by:gmanpert
ID: 17839741
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

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question