working with time date in a query

[Bruce]

Hi,

I have a time field in myTable of type text that has the date and time of
day for each record.

e.g. 3/03/2008 4:10:11 PM.

I want to be able to Group the data based on an interval, say for each 15min
of the day in my query. It would show results like
3/03/08 4:15:00 PM
3/03/08 4:30:00 PM
3/03/08 4:45:00 PM
3/03/08 5:00:00 PM

so far i;ve worked out that I need to convert to text date to a real date
with
CDate(myDateField)

What should I do next?

Bruce

 

[Golfinray]

If you can't change the text type of the table to date, you might try
Minutes:datediff("m",[your first date field],[your second date field]) That
should give you time in minutes which you could then covert to hours or days
if you wanted.

 

[John Spencer]

(Minute(CDate(myDateField)) \15) * 15 will return 0,1,2, or 3

So one solution might be the following.

DateAdd("n",((Minute(CDate(myDateField))\15)*15)-(Minute(CDate(myDateField)),CDate(myDateField)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

If you can't change the text type of the table to date, you might try
Minutes:datediff("m",[your first date field],[your second date field]) That
should give you time in minutes which you could then covert to hours or days
if you wanted.

Hi,

I have a time field in myTable of type text that has the date and time of
day for each record.

e.g. 3/03/2008 4:10:11 PM.

I want to be able to Group the data based on an interval, say for each 15min
of the day in my query. It would show results like
3/03/08 4:15:00 PM
3/03/08 4:30:00 PM
3/03/08 4:45:00 PM
3/03/08 5:00:00 PM

so far i;ve worked out that I need to convert to text date to a real date
with
CDate(myDateField)

What should I do next?

Bruce

 

[Bruce]

Hi John,

Just had to add a couple of brackets and im almost there. I just noticed I
need to est the seconds to 00.

How could I rounddown the seconds to 00?

Bruce

DateAdd("n",((Minute(CDate([time]))\15)*15)-(Minute(CDate([time]))),CDate([time]))

(Minute(CDate(myDateField)) \15) * 15 will return 0,1,2, or 3

So one solution might be the following.

DateAdd("n",((Minute(CDate(myDateField))\15)*15)-(Minute(CDate(myDateField)),CDate(myDateField)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

If you can't change the text type of the table to date, you might try
Minutes:datediff("m",[your first date field],[your second date field]) That
should give you time in minutes which you could then covert to hours or days
if you wanted.

Hi,

I have a time field in myTable of type text that has the date and time of
day for each record.

e.g. 3/03/2008 4:10:11 PM.

I want to be able to Group the data based on an interval, say for each 15min
of the day in my query. It would show results like
3/03/08 4:15:00 PM
3/03/08 4:30:00 PM
3/03/08 4:45:00 PM
3/03/08 5:00:00 PM

so far i;ve worked out that I need to convert to text date to a real date
with
CDate(myDateField)

What should I do next?

Bruce

 

[John Spencer]

Ok, this is getting way to complex. There must be an easier way.

To strip of the seconds, you could use something like:
DateAdd("s",-Second(DateValue),DateValue)

You might try the following expression which should run down to the preview
times of 00, 15, 30, or 45 minutes - with no seconds appended.

CDate(Int((CDbl(#1/1/98 07:59:30#) * 1440)/15)* 15/1440)
That will return 1/1/98 07:45:00

If you want to round up you can use
CDate(-Int(-(CDbl(#1/1/98 07:59:30#) * 1440) / 15) * 15/ 1440)

There is a lot of arithmetic going on there and you may get some improper
rounding in rare cases where the time is right on the edge of changing into
the next increment. I don't think this will happen, but it is a remote
possibility.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Hi John,

Just had to add a couple of brackets and im almost there. I just noticed I
need to est the seconds to 00.

How could I rounddown the seconds to 00?

Bruce

DateAdd("n",((Minute(CDate([time]))\15)*15)-(Minute(CDate([time]))),CDate([time]))

(Minute(CDate(myDateField)) \15) * 15 will return 0,1,2, or 3

So one solution might be the following.

DateAdd("n",((Minute(CDate(myDateField))\15)*15)-(Minute(CDate(myDateField)),CDate(myDateField)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

If you can't change the text type of the table to date, you might try
Minutes:datediff("m",[your first date field],[your second date field]) That
should give you time in minutes which you could then covert to hours or days
if you wanted.

:

Hi,

I have a time field in myTable of type text that has the date and time of
day for each record.

e.g. 3/03/2008 4:10:11 PM.

I want to be able to Group the data based on an interval, say for each 15min
of the day in my query. It would show results like
3/03/08 4:15:00 PM
3/03/08 4:30:00 PM
3/03/08 4:45:00 PM
3/03/08 5:00:00 PM

so far i;ve worked out that I need to convert to text date to a real date
with
CDate(myDateField)

What should I do next?

Bruce