Pull Date from Date/Time Field

[moody3829]

I have a table that has dtOpenDateTime and dtClosedDateTime fields. These fields contain dates and times the record was opened and closed in a format such as, 2/27/2010 11:34:15 AM. What I am trying to do is get a WHERE clause in a query that will pull records that have the same open and closed date, ignoring the time.

I would think this is sime syntax, but I can't figure it out.

"Select Count(tblDailyTickets.txtCallNum) As intFCR, tblDailyTickets.txtOwnerID " & _
"FROM tblDailyTickets " & _
"WHERE dtOpenDateTime Between DateSerial(2010,Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)" & _
"AND tblDailyTickets.txtIncidentNum Is Null " & _
"AND CLAUSE TO COMPARE JUST THE DATES"

Can someone point me in the right direction?

Thanks

 

[Galaxiom]

In SQL you need to use date delimiters and MM/DD/YYYY format like this:
#MM/DD/YYYY#

There are easier ways then concocting the date as you have with DateSerial and the date parsing functions.

Format({date expression}, "\#MM/DD/YYYY\#")

Concatenate the parts of this you require with the bits you need to change.

Date comparisions look just like any other comparison so long as the correct format is used.

 

[Mr. B]

Try extracting only the date from the StartDateAndTime and the EndDateAndTime fields using an sql statement like this:

SELECT tblTest.StartDate, DateSerial(Year([tblTest]![StartDate]),Month([tblTest]![StartDate]),Day([tblTest]![StartDate])) AS StartDateOnly, tblTest.EndDate, DateSerial(Year([tblTest]![EndDate]),Month([tblTest]![EndDate]),Day([tblTest]![EndDate])) AS EndDateOnly
FROM tblTest
WHERE (((DateSerial(Year([tblTest]![StartDate]),Month([tblTest]![StartDate]),Day([tblTest]![StartDate])))=#3/17/2010#) AND ((DateSerial(Year([tblTest]![EndDate]),Month([tblTest]![EndDate]),Day([tblTest]![EndDate])))=#3/17/2010#));

This will only return values where the two date values (not time considered) are the same.

 

[Galaxiom]

That is way clumsy using DateSerial to extract the date only. Format is a better option. However the difference in date can be done in a single step.

Derive a field in the query finding the difference in days between start and end date/time.

DateDiff("d",[startdate],[enddate]) AS DaysDifference
Then the where clause.
WHERE DaysDifference=0

 

[namliam]

That is way clumsy using DateSerial to extract the date only. Format is a better option. However the difference in date can be done in a single step.

DateSerial may be the long way around but using Format isnt the best way either (sorry)

You want to keep the dates as much untouched as possible, in particular if this is an indexed column.

You will be generaly much better of to do a search for

WHERE [tblTest]![StartDate] between #3/17/2010# and #3/17/2010 23:59:59# 
AND [tblTest]![EndDate] between #3/17/2010# and #3/17/2010 23:59:59#

Or

WHERE [tblTest]![StartDate]>=#3/17/2010# 
AND [tblTest]![StartDate]<#3/18/2010#
AND [tblTest]![EndDate]>=3/17/2010#
AND [tblTest]![EndDate]<3/18/2010#

Which is the same as:

WHERE [tblTest]![StartDate] between #3/17/2010# and #3/17/2010# + #23:59:59#
AND [tblTest]![EndDate] between #3/17/2010# and #3/17/2010# + #23:59:59#

WHERE [tblTest]![StartDate]>=#3/17/2010# 
AND [tblTest]![StartDate]<#3/17/2010# + 1
AND [tblTest]![EndDate]>=3/17/2010#
AND [tblTest]![EndDate]<3/17/2010# + 1

Also if for display purposes you want to 'trim off' the time, you can either simply use the format option of the query/form to only display the date...
Or use Int(yourdate)

In the OP's question of comparing two date fields Int(startdate) = Int(enddate) will do just fine

Also Lookup the dateadd function in the access help to replace
DateSerial(2010,Month(Date())-1,1)
and to calculate the last day of the previous month, instead of DateSerial(Year(Date()),Month(Date()),0)":
Date() - Day (date())

DO TAKE NOTE, that any time on the last day of the month will be greater than your last day.
i.e.
28-feb-2010 11:13:15 am will not be selected when you do
Between 01-feb-2010 and 28-feb-2010
Instead you need/want/will have to do something like above either select the next day (01-Mar-2010) or add the last second of the day (23:59:59)

Good luck !

 

[raskew]

Hi -

Take a look at the DateValue() function., e.g.
x = now()
? x
3/18/2010 5:22:36 PM
? datevalue(x)
3/18/2010

Bob

 

[vbaInet]

I'm with raskew on this one . It's a much elegant solution.

WHERE DateValue([Date1]) = DateValue([Date2])

 

[Mr. B]

I have to agree with vbaInet and raskew. I didn't even think about DateValue but that is an excellent solution.

I will need to remember that one.