converting date time field

Hi,

I'm trying to report on the time records are created , but not to include the date. So for a month long period, I want to know how many records have been created between 8am & 9am. I can group the records and display by hour, but as the database is a time date field, it displays for each date as well.

I think I probably need to create a formula that will strip out the date information, then I can group by hour and that will return what I need, but I have no idea how create such a formula...

Any ideas?

Thanks,

Matt.What is your Database?
Write a Stored Procedure having the query group by Time and use that sp to design the report

Convert seconds to Hours:Minutes:Seconds

Hi,
I have a table column that stores time in seconds and I need to convert the
total time for a given period into hours:minutes:seconds. My data source is
a SQL table but the report delivery tool is Cognos, I was trying to do this
in Cognos, but it has become very cumbersome. I'm thinking of creating a SQ
L
view - how would the syntax flow for converting the time?
Thanks in advance!Patrice wrote:
> Hi,
> I have a table column that stores time in seconds and I need to
> convert the total time for a given period into hours:minutes:seconds.
> My data source is a SQL table but the report delivery tool is Cognos,
> I was trying to do this in Cognos, but it has become very cumbersome.
> I'm thinking of creating a SQL view - how would the syntax flow for
> converting the time?
>
divide by 3600 for the hours, divide the modulus of the previous by 60 for
the minutes, use modulus for the leftover seconds
declare @.secs int
declare @.hms varchar(10)
set @.secs=7532
set @.hms=
cast(@.secs/3600 as varchar(14)) + ':' +
right('0' + cast((@.secs % 3600)/60 as varchar(2)),2) + ':' +
right('0' + cast(@.secs % 60 as varchar(2)),2)
print @.hms
HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||http://www.aspfaq.com/2271
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:A9A2DF31-D629-423E-B324-657F3B8E6684@.microsoft.com...
> Hi,
> I have a table column that stores time in seconds and I need to convert
> the
> total time for a given period into hours:minutes:seconds. My data source
> is
> a SQL table but the report delivery tool is Cognos, I was trying to do
> this
> in Cognos, but it has become very cumbersome. I'm thinking of creating a
> SQL
> view - how would the syntax flow for converting the time?
> Thanks in advance!|||"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:A9A2DF31-D629-423E-B324-657F3B8E6684@.microsoft.com...
> Hi,
> I have a table column that stores time in seconds and I need to convert
> the
> total time for a given period into hours:minutes:seconds. My data source
> is
> a SQL table but the report delivery tool is Cognos, I was trying to do
> this
> in Cognos, but it has become very cumbersome. I'm thinking of creating a
> SQL
> view - how would the syntax flow for converting the time?
> Thanks in advance!
This will work, as long as you have less than a month worth of seconds:
declare @.seconds int
set @.seconds = 2591999
select convert(char(8), dateadd(s, @.seconds, '19000101'), 8)