By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,910 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

count hours within months

P: n/a
wz
Hi,
I'm new to access sql, and have a simple question.
I have a date field, named log_dt, and I'm trying to count how many
hours each record is from starting time of it's own month. But the 5
in #5/1/2003# need to be changed according to the record, not hard
coded. How can I achieve that? Is there a date function that I can
create a date with specified month and day?

SELECT
DateDiff("h",#5/1/2003#,[LOCAL DATA].[LOG_DT]) AS hours,
DateDiff("m",#1/1/2003#,[LOCAL DATA].[LOG_DT])+1 AS months,
FROM [LOCAL DATA]
thanks a lot,

Wei
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Sounds as though you want the DateSerial function.

Assuming you're trying to get the first for each month of Log_Dt, you'd need
something like:

SELECT
DateDiff("h",DateSerial(Year([LOG_DT], Month([LOG_DT]), 1),[LOG_DT]) AS
hours,
DateDiff("m",DateSerial(Year([LOG_DT], 1, 1),[LOG_DT])+1 AS months,
FROM [LOCAL DATA]
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"wz" <zw****@gmail.com> wrote in message
news:89**************************@posting.google.c om...
Hi,
I'm new to access sql, and have a simple question.
I have a date field, named log_dt, and I'm trying to count how many
hours each record is from starting time of it's own month. But the 5
in #5/1/2003# need to be changed according to the record, not hard
coded. How can I achieve that? Is there a date function that I can
create a date with specified month and day?

SELECT
DateDiff("h",#5/1/2003#,[LOCAL DATA].[LOG_DT]) AS hours,
DateDiff("m",#1/1/2003#,[LOCAL DATA].[LOG_DT])+1 AS months,
FROM [LOCAL DATA]
thanks a lot,

Wei

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.