Date / Time field help (should be easy this one!)

[robjones23]

Hi,

I've got a database with the date / time set as: dd/mm/yy hh/mm/ss, what i need is just the date and the hour,

so 22/12/04 15:13:15 would read 22/12/04 15:00:00

I don't need it to round up (e.g. if it was 15:32, i don't want it to display as 16:00).

I'm guessing this is easy but I'm stumped

cheers,

Rob.

EDIT: - Just so you know, I can't just format the original column, it MUST be a new column with the specifics above.

 

[KenHigg]

So you just want to extract this for use in a report?

kh

 

[robjones23]

hmmm, no not really,

It's going to be used for a pivot chart to represent data coming in per hour with spikes (in this case the spikes relate to seconds / milliseconds).

The databse tracks messages sent to a server and the response time. There's two tables, one with sent / received times my side of the network, and another with sent / received times after a firewall. I'm trying to show graphically the difference between the two to prove that data leaves me within a couple of seconds of receiving a request but then sits behind the firewall somewhere before receiving a response.

As this is a random occurance (sometimes messages come out of the firewall within .5 seconds, sometimes they take 59 seconds I need to be able to account for the spikes in my results.

does that make sense?

 

[robjones23]

I'm expecting to need something like this:

DATE: Left([Receivedatetime_inbound],InStr(1,[Receivedatetime_inbound]," "))

This code changes the date / time field to display just the date but I don't know how to change it to make it display date / hour.

 

[reclusivemonkey]

Well you can take the first part of the date field (where this is [Date]);

Left([Date],8)

then add the first two digits of your hour field,

Mid([Date],10,2)

Then just add some zeros. This will be a string though, so if you need it as a date, you'll have to convert it back to a date.

CDate(Left([Date],8)+Mid([Date],10,2)+"00:00")

Not 100% sure on that, but it should work!

 

[KenHigg]

Could you get something like the following to work:

hours: DatePart("h",[time])

???
kh

 

[robjones23]

CDate(Left([Date],8)+Mid([Date],10,2)+"00:00")

Not 100% sure on that, but it should work!

Hmmmm, this ALMOST works, it returns 16/12/204 00:00 as an example of 16/12/2004 hhmmss

 

[robjones23]

Nevermind I got it -

CDate: Left([month / date / time],9)+Mid([month / date / time],10,4)+":00"

I only need the hours so I took off the first "00"

Thanks for all the help reclusivemonkey it's much appreciated!

cheers,

Rob.

 

[Jon K]

Date Hour: DateValue([DateTime])+CDate(DatePart("h",[DateTime]) & ":0:0")

which returns a date/time field (not a text field) and does not rely on the display format of the original date/time field.
.