Solved

# Convert numeric to datetime

Posted on 2007-10-17
2,275 Views
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
Question by:marcus72

LVL 26

Expert Comment

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

LVL 26

Expert Comment

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 Comment

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

LVL 26

Expert Comment

Try this:

SELECT
FROM
YourTable
0

LVL 68

Expert Comment

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

LVL 68

Accepted Solution

--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

LVL 26

Expert Comment

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 Comment

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

LVL 26

Expert Comment

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

LVL 26

Expert Comment

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 Comment

It's a datetime datatype.
0

LVL 26

Expert Comment

show me the statement you used, please.
0

LVL 68

Expert Comment

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

LVL 26

Expert Comment

Ok, I made a slight modiffication:

SELECT
FROM
YourTable

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

LVL 26

Assisted Solution

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

LVL 26

Expert Comment

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

Author Comment

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.