Date/Time Query 2

[smallcheese]

Can anyone help on how to query a single date time field like this:
SELECT * FROM xxx WHERE DateTimeField = 'Monday', AND DateTimeField IS BETWEEN 12/9/2002 AND 22/11/2002 AND DateTimeField IS BETWEEN 9:50 AM AND 10:40 AM

To give you an idea, this is a print log, so I wish to get a summary of all jobs which were printed on mondays only, between 9:50 and 10:40, and that occured between the specified dates.
Thanks guys.

 

[mwolf00]

SELECT * FROM myTable WHERE
datePart("w",dateField) = 2
AND
dateField BETWEEN #9/12/2002# AND #11/22/2002#
AND
(datePart("h",dateField) >= 9 AND datePart("n",dateField) >= 50)
AND
(datePart(&quot;h&quot;,dateField) <= 10 AND datePart(&quot;n&quot;,dateField) <= 40) Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048

mikewolf@tst-us.com

 

[r937]

might not be the most efficient, but the FORMAT function can do each of those conditions:

where format(DateTimeField,'dddd')='Monday'
and format(DateTimeField,'yyyymmdd')
between '20020601' and '20030701'
and format(DateTimeField,'hhnn')
between '0950' and '1040'

rudy

 

[r937]

oops wrong dates, but you get the idea

(-:

 

[smallcheese]

To the first responder, I have tried this, but when you do a query that says >= 50 AND <=40 you don't get many results.

To the second responder, I am not aware of any format command in SQL, I don't think this works.

Please keep on trying though guys, I really need this one sorted!!!

 

[mwolf00]

SELECT * FROM myTable WHERE
datePart(&quot;w&quot;,dateField) = 2
AND
dateField BETWEEN #9/12/2002# AND #11/22/2002#
AND use parenthesis to separate the following 2 groups of criteria
(datePart(&quot;h&quot;,dateField) >= 9 AND datePart(&quot;n&quot;,dateField) >= 50) This evaluates to greater than 9:50
AND
(datePart(&quot;h&quot;,dateField) <= 10 AND datePart(&quot;n&quot;,dateField) <= 40) This evaluates to less than 10:40
Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048

mikewolf@tst-us.com

 

[r937]

> &quot;I am not aware of any format command in SQL,
> I don't think this works&quot;

did you try it?

of course FORMAT is not standard sql

it's access

this is the access forum, after all

and yes, it works, i tested it in access97 to be sure, which is why i inadvertently pasted a date range different from the one you presented


rudy

 

[smallcheese]

Thanks guys. Once I had paid attention to what you had both said, you both contributed.

Cheers.