marcus72
asked on
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
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
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?
AVG(CAST(CAST(CONVERT(char
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
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?
Zberteoc
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?
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?
2007-10-16 10:30:00.000
and
2007-10-17 18:45:00.000
what would you think the average is?
ASKER
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
10:30:00.000
and
18:45:00.000
Try this:
SELECT
substring(convert(varchar(
FROM
YourTable
SELECT
substring(convert(varchar(
FROM
YourTable
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
as numeric(18, 4)) * 24)
, 100), 7)
Try this (if you want HH:MM only):
RIGHT(CONVERT(VARCHAR(40),
AVG(CAST(CAST(CONVERT(char
as numeric(18, 4)) * 24)
, 100), 7)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
substring(convert(varchar(
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.
SELECT
substring(convert(varchar(
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.
ASKER
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:
right(dateadd(ss,avg(conve
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:
right(dateadd(ss,avg(conve
Is your column of datetime type or some string type?
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.
ASKER
It's a datetime datatype.
show me the statement you used, please.
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.
Ok, I made a slight modiffication:
SELECT
substring(convert(varchar(
FROM
YourTable
and I used it against a table with 4 millions rows and worked.
SELECT
substring(convert(varchar(
FROM
YourTable
and I used it against a table with 4 millions rows and worked.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ignore the TOP 10, i used it in my tests.
ASKER
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.