Solved

Matching Date dd-mmm-yy

Posted on 2006-10-26
12
312 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
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 16

Expert Comment

by:GreymanMSC
Comment Utility
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
Comment Utility
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 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 400 total points
Comment Utility
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
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
Leigh,
the query defaults the format of date  to mm/dd/yyyy
0
 
LVL 16

Expert Comment

by:GreymanMSC
Comment Utility
IE: It eliminates any Time Values.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Is the field a date type field?
0
 

Author Comment

by:gmanpert
Comment Utility
Yes, the field was an Access Date field.

-g
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
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
Comment Utility
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now