Stumped on date conversions 2

[fergman]

I thought this was simple, but my query yields a datetime value in 12 hour format and I can not get it to convert to 24hour (and I do need it in 24 hour).
The query below has three commented sections, and I denoted the steps, originally it is 24 hr, I convert it to float, average it convert it back and it's stuck in 12 hour.

I've tried several things to force it, including convert(datetime, Inserted,114)
I went through the help files list of conversions and tried all the styles that are labeled 24hr.

I am officially stumped.

query:
--select Inserted --24hr 2009-02-26 18:05:01.467
--select cast(Inserted as float)
--select avg(cast(Inserted as float))
select cast(avg(cast(Inserted as float)) as datetime) --12hr 2009-03-05 06:05:01.657
from Tablename
where
AND stagetime > getdate() - 14
AND stagetime < getdate()

 

[r937]

CONVERT([blue]VARCHAR[/blue],Inserted,120)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[gmmastros]

I don't see anything wrong with what you are doing. But... you need to realize that Date AND Time will be averaged, not just time.

For example, if you have a table that shows what time you start work...

Declare @Temp Table(StartDateTime DateTime)

Insert Into @Temp Values('20090223 18:05:01.467')
Insert Into @Temp Values('20090224 18:05:01.467')
Insert Into @Temp Values('20090225 18:05:01.467')
Insert Into @Temp Values('20090226 18:05:01.467')
Insert Into @Temp Values('20090226 18:05:01.467')


Select Convert(DateTime, Avg(Convert(Float, StartDateTime)))
From   @Temp

Select Convert(DateTime, Avg(Convert(Float, StartDateTime - DateAdd(Day, DateDiff(Day, 0, StartDateTime), 0))))
From   @Temp

The first query averages date and time. The second query removes the data component and averages just the time.

Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fergman]

Thanks to both of you, I had suspected the date was being an issue, but hadn't yet stripped it off. The varchar convert with the 120 style also made sure it stayed in the right format. I could have sworn I tried ,120 style last night but maybe I didn't.

I appreciate the help!