Date Parameter

  • Thread starter Thread starter buzzandbeyond
  • Start date Start date
B

buzzandbeyond

Hi,

I have a field in a table that is dd/mm/yy hh:mm:ss and I need to
return everything that is for today's date. I tried using date() but
this does not work.

Any tips?

Cheers
Dave
 
Dave

Are you saying your field had both date & time in it (you described a
format, not the contents).

If you want to find all the records where a date/time value has today's
date, you need to "calculate" the date-portion of the field. In your query,
you could use a (new) field something like:

NewField: DateValue([YourTablesDateTimeField])

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Using Date() it has the the date and time as of midnight. You will need
to strip the time off of your data to match.

Try this --
Int([YourDateTimeField])

Then use criteria of Date() to select your records.
 
Hi,

I have a field in a table that is dd/mm/yy hh:mm:ss and I need to
return everything that is for today's date. I tried using date() but
this does not work.
Click to expand...

That's because #7/31/2006 00:00:00# is the value of Date(), and that
will not match #7/31/2006 11:21:46# which might be in your table.

Either use DateValue([datefield]) as a calculated field, or - to take
advantage of any index on the date field - use a criterion of
= Date() AND < Date() + 1
Click to expand...

John W. Vinson[MVP]
 
Plus one what <g>?

For something perhaps more logical:

WHERE arg_date BETWEEN DATE() AND DATE() + TIMESERIAL(23:59:59)

Jamie.

--
 
Jamie said:
For something perhaps more logical:

WHERE arg_date BETWEEN DATE() AND DATE() + TIMESERIAL(23:59:59)
Click to expand...

Oops!

WHERE arg_date BETWEEN DATE() AND DATE() + #23:59:59#

Jamie.

--
 
Plus one what <g>?
Click to expand...

Plus one day... overreliance on the implementation, of course!

Your idea would be fine if you can count on no data DURING the last
second - date/time values can contain sub-second data but not display
it. Unlikely I know!

Cleaner would be
= Date() AND < DateAdd("d", 1, Date())
Click to expand...

John W. Vinson[MVP]
 
John said:
Plus one day... overreliance on the implementation, of course!

Cleaner would be
Click to expand...

Agreed :)
Your idea would be fine if you can count on no data DURING the last
second - date/time values can contain sub-second data but not display
it.
Click to expand...

The way I see it, sub-second datetime values are not supported by Jet's
temporal functions (e.g. DATEPART) so by going outside of the supported
functionality you would either have to go it on your own (e.g. write
your own temporal functions) or put up with rounding (banker's rounding
for datetime values <g>?!) which could be acceptable e.g.

where 0.000011574074074074074074 (decimal) is one second in DATETIME
representation:

SELECT DATEPART('d',
#2006-01-01 23:59:59#
+ (0.49 * 0.000011574074074074074074)
)

is 1 (rounds down),

SELECT DATEPART('d',
#2006-01-01 23:59:59#
+ (0.50 * 0.000011574074074074074074)
)

is 1 (rounds down),

SELECT DATEPART('d',
#2006-01-01 23:59:59#
+ (0.51 * 0.000011574074074074074074)
)

is 2 (rounds up).

Personally, I use CHECK constraints (validation rules) on my DATETIME
columns to ensure they are of one second granularity e.g.

CREATE TABLE Test (
test_date DATETIME NOT NULL,
CONSTRAINT test_date__one_second_granularity
CHECK (
test_date =
DATESERIAL(YEAR(test_date), MONTH(test_date), DAY(test_date))
+ TIMESERIAL(HOUR(test_date), MINUTE(test_date), SECOND(test_date))
)
);

with helper functions to remove (round) subsecond elements e.g.

CREATE PROCEDURE TestProcAdd (
arg_date DATETIME
) AS
INSERT INTO Test (test_date)
SELECT DISTINCT
DATESERIAL(YEAR(arg_date), MONTH(arg_date), DAY(arg_date))
+ TIMESERIAL(HOUR(arg_date), MINUTE(arg_date), SECOND(arg_date))
AS test_date
FROM Test;

Jamie.

--
 
Back
Top