• Status: Solved
• Priority: Medium
• Security: Public
• Views: 2317

# Convert numeric to datetime

Hello, I am trying to get the average time out of a datetime column.  I am able to get the average by casting to a numeric data time.  Here is how I'm doing that:

AVG(CAST(CAST(CONVERT(char(8), datetimetoavg, 108) as datetime)
as numeric(18, 4)) * 24)

So this will give me an average number that looks like this:  12.006089 or
0.525120
which is 12pm or 12:25am respectively

now I'd like to take these numbers and convert them back to a proper datetime format.  I've tried:

right(convert(datetime, AVG(CAST(CAST(CONVERT(char(8), datetimetoavg, 108) as datetime)
as numeric(18, 4)) * 24), 112), 8)

But this doesn't seem to be distinguishing between am or pm.

Would anyone be able to help me convert the numeric time value back to a datetime format or has any other ideas to find the average time from a datetime column?
0
marcus72
• 10
• 4
• 3
2 Solutions

Commented:
What actually do you mean with average time value? is a datetime value that is inserted repeatedly in a column and you would like to get somewhere in the middle? Do you care about the date part or only the time?
0

Commented:
If you have 2 datetimes:

2007-10-16 10:30:00.000
and
2007-10-17 18:45:00.000

what would you think the average is?
0

Author Commented:
I'm just looking at the time portion so i want to exclude the date parts so in your example it would be the average of:
10:30:00.000
and
18:45:00.000
0

Commented:
Try this:

SELECT
FROM
YourTable
0

Senior DBACommented:
Format 8 always uses 24-hour time (18:00 = 06:00PM).

Try this (if you want HH:MM only):

RIGHT(CONVERT(VARCHAR(40),
AVG(CAST(CAST(CONVERT(char(8), datetimetoavg, 108) as datetime)
as numeric(18, 4)) * 24)
, 100), 7)
0

Senior DBACommented:
--HH:MMxx
RIGHT(CONVERT(VARCHAR(40),
CAST(AVG(CAST(CAST(CONVERT(char(8), datetimetoavg, 108) as datetime)
as numeric(18, 4)) * 24) as datetime)
, 100), 7)

--HH:MM:SSxx
STUFF(RIGHT(CONVERT(VARCHAR(40),
CAST(AVG(CAST(CAST(CONVERT(char(8), datetimetoavg, 108) as datetime)
as numeric(18, 4)) * 24) as datetime)
, 109), 14), 9, 4, '')
0

Commented:
With my solution you don't need to convertanything or to calculate ahead of time, you just apply that formula to any datetime column in any table. So if yoo have a table called YourTable and a datetime column called date_column just so this:

SELECT
FROM
YourTable

this will return in format HH:MM:SS.mmm, if you want HH:MM:SS just replace last 12 with 8 or HH:MM replace last 12 with 5.
0

Author Commented:
Zberteoc, Scott,
I'm actually getting the same error message with both your solutions
error:  Conversion failed when converting datetime from character string.
I was able to get what I want with:
0

Commented:
Is your column of datetime type or some string type?
0

Commented:
My solution applied to a datetime column will work. If your column is not of datetime it may contain values that are nor in a correct datetime format. When those are converted generate error. That's it. What you need is to try to identify the bad data and try to convert it to correct datetime formats.
0

Author Commented:
It's a datetime datatype.
0

Commented:
show me the statement you used, please.
0

Senior DBACommented:
Please be sure you used my last version, where I did code the needed CAST( ... AS DATETIME), and not the earlier version(s) where I mistakenly left it off.
0

Commented:
Ok, I made a slight modiffication:

SELECT
FROM
YourTable

and I used it against a table with 4 millions rows and worked.
0

Commented:
Sorry, marcus, here is the statement that will give you the right answer:

SELECT TOP 10
FROM
YourTable

I added the average of (miliseconds from each date midnight) to getdate() and not to ( the start fo getdate()), which is today 00:00:00.000) and that was wrong. Now you will get the right average time. Test it on few record where you know the average time.
0

Commented:
ignore the TOP 10, i used it in my tests.
0

Author Commented:
Thank you Zberteoc and Scott both your solutions worked equally well for me I really appreciate the time you took to help me with this.
0

## Featured Post

• 10
• 4
• 3
Tackle projects and never again get stuck behind a technical roadblock.