MS Access query dates

[shocker-z]

I need to pull up records on dats but the format of the dates in the database are "21/05/2007 17:01:54" so my query

 

SELECT Maintenance_ID

FROM Maintenance_Dbf

WHERE [Date Logged] = $today

 

 

Will not return any results.. do i need to somehow say if it's greater than..? and add 00:00:00 so everything today is greater than 21/05/2007 00:00:00?

 

 

i have tryed LIKE $today as well as trying to use % in the statement but all with no joy..

 

Heres my code:

 

<?php
include('header.php');
$today='21-05-2007';
echo 'today: '.$today;
$selectcallstoday="SELECT Maintenance_ID
      FROM Maintenance_Dbf
  WHERE [Date Logged] LIKE $today";
$callstoday=odbc_exec($conn,$selectcallstoday);
if (!$callstoday) {
exit("Error in selecting Account Manager data: ".odbc_errormsg());
}
while($calls=odbc_fetch_array($callstoday)) {
echo $calls['Maintenance_ID'];
}

?>

 

 

header.php

 

<?php
$conn=odbc_connect('pronetlocal','','');
if (!$conn) {
exit("Connection Failed: " . $conn);
}
?>

 

Regards

Liam

[per1os]

I think you need the # sign for dates and I am not sure if the like operator can be used on them...

 

<?php
include('header.php');
$today='#21-05-2007#'; // added pound sign here
echo 'today: '.$today;
$selectcallstoday="SELECT Maintenance_ID
      FROM Maintenance_Dbf
  WHERE [Date Logged] LIKE '$today'"; // added single quotes here. may need to be removed.
$callstoday=odbc_exec($conn,$selectcallstoday);
if (!$callstoday) {
exit("Error in selecting Account Manager data: ".odbc_errormsg());
}
while($calls=odbc_fetch_array($callstoday)) {
echo $calls['Maintenance_ID'];
}

?>

 

So yea I would verify the LIKE keyword and probably the single quotes, I know access requires the # sign around the dates.

[Barand]

As far as I can remember, the #'s round the date replace the single quotes for dates, so

$selectcallstoday="SELECT Maintenance_ID
      FROM Maintenance_Dbf
  WHERE [Date Logged] = $today";

 

so you get a query looking like this

 

SELECT Maintenance_ID

      FROM Maintenance_Dbf

  WHERE [Date Logged] = #21-05-2007#

[shocker-z]

still returning no results :S

[Barand]

I just ran this on one of my old access dbs and it works fine

 

<?php
$cnx = odbc_connect ('obs3','','');

$date = '31-01-2001';

$sql = "SELECT TOP 100 * FROM tktsales
        WHERE date_sold = #$date#";

$res = odbc_exec($cnx, $sql);

while (odbc_fetch_into($res, $a)) {
    echo '<pre>', print_r($a, true), '</pre>';
}

?>

[shocker-z]

hmm still not returning any results..

 

Are you sure you date in the database is in the same format? DD-MM-YYYY HH:MM:SS

 

If i take the WHERE clause out then here is an examplke of result to show data type..

 

 

Array
(
    [0] => 3365
    [1] => Nottingham Community Housing Ass
    [2] => 
    [3] => 2003-07-21 16:28:39
    [4] => 2003-07-21 16:28:39
    [5] => 11
    [6] => Bob
    [7] => 0
    [8] => Please reload PC in Design Department like the last time AM did it.
    [9] => 
    [10] => 23
    [11] => 2003-07-22 00:00:00
    [12] => 
    [13] => 1899-12-30 12:20:00
    [14] => 2003-07-22 00:00:00
    [15] => 1899-12-30 14:17:00
    [16] => Collect x2 15" monitors + 2 NEO's from Lenton 11.45 - 12.05. Install latest catalyst 3.6 drivers tweaked GFX toward performance - this is for games. Check scanner on maint1 no fault found scanning perfectly. Call closed.
    [17] => 0
    [18] => 0
    [19] => 1
    [20] => 
    [21] => 
    [22] => 
    [23] => 
    [24] => 0
    [25] => 
    [26] => 
    [27] => 
)

 

 

I tryed a date straight out of database:

 

<?php
$cnx = odbc_connect ('pronetlocal','','');

$date = '2001-01-02 10:12:08';

$sql = "SELECT * FROM Maintenance_Dbf
        WHERE [Date Logged] = #$date#";

$res = odbc_exec($cnx, $sql);

while (odbc_fetch_into($res, $a)) {
    echo '<pre>', print_r($a, true), '</pre>';
}

?>

 

and that bring it up but if i remove ' 10:12:08' then it returns nothing.

 

Regards

Liam

[Barand]

Date looks the same on mine, except no time element

Array
(
    [0] => 2001-01-31 00:00:00
    [1] => 14
    [2] => 367010
    [3] => 9926.0
    [4] => MET
    [5] => 729
    [6] => 2657.0
    [7] => 2658.0
    [8] => 1.0
    [9] => 48.5
    [10] => S
    [11] => 0
)

 

Does ... date BETWEEN #21-07-2003 00:00:00# AND #22-07-2003 00:00:00# ... pull any data?

[shocker-z]

Sorted it now

 

 

$sql = "SELECT * FROM Maintenance_Dbf

        WHERE ((Maintenance_Dbf.[Date Closed]) = #$date#)";

 

 

Weird hownit wouldn't work outside the brackets.. removed them and it still works :S

 

$sql = "SELECT TOP 100 * FROM Maintenance_Dbf

        WHERE [Date Closed] = #$date#";

 

that works fine.. wth?

 

lol

 

main thing is that it is now working

 

 

Thanks Barand

 

 

 

Liam

[shocker-z]

hmm not solved just realised i was lookinh up Date Closed' instead of 'Date Logged' here are the field types and so on of each of the fields..

 

 

Date Logged - Short Date

Date Closed - Medium Date

 

 

Is there a chance that it is a different syntax or somthing for short date?

 

 

Regards

Liam

[per1os]

Yes short date is just the date IE: 21-07-2003  Long date has the hours minutes and seconds attached to it.

[shocker-z]

tried it.. when i click on it in acess it does show the 00:00:00 too tho :S

 

I've tryed DD-MM-YYY and YYYY-MM-DD with and without the hashes the weird thing is that it works with the long date but not the short..

[Barand]

Have you tried US format, MM-DD-YYYY, just in case.

[shocker-z]

still no joy mate..

 

I'm thinking that there must be somthing different with the shortdate but aint a clue what as the queries dont return results in access either.

[Barand]

What about ../../.. separators instead of "-"?

[shocker-z]

no joy

 

so far i have tried..

 

2007-05-21

2007-21-05

21-05-2007

05-21-2007

 

07-05-21

07-05-21

21-05-07

05-21-07

 

2007/05/21

2007/21/05

21/05/2007

05/21/2007

 

07/05/21

07/05/21

21/05/07

05/21/07

 

 

and no joy..

[Barand]

#21-MAY-07# ?

[shocker-z]

nope

 

just to tell u that in access the date for date logged looks like this: 23/05/2007 15:50:38

 

Liam