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

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
Asked:
marcus72
  • 10
  • 4
  • 3
2 Solutions
 
ZberteocCommented:
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
 
ZberteocCommented:
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
 
marcus72Author 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
ZberteocCommented:
Try this:

SELECT
      substring(convert(varchar(23),dateadd(ms,avg(datediff(ms,DATEADD(dd,DATEDIFF(dd,0,date_column),0),date_column)),getdate()),121),12,12) AS AverageHour
FROM
      YourTable
0
 
Scott PletcherSenior 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
 
Scott PletcherSenior 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
 
ZberteocCommented:
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(23),dateadd(ms,avg(datediff(ms,DATEADD(dd,DATEDIFF(dd,0,date_column),0),date_column)),getdate()),121),12,12) AS AverageHour
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
 
marcus72Author 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:
right(dateadd(ss,avg(convert(float(53),dateadd(dd,datediff(dd,Datetimetoavg,0),Datetimetoavg)))*24*60*60,0), 7)
0
 
ZberteocCommented:
Is your column of datetime type or some string type?
0
 
ZberteocCommented:
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
 
marcus72Author Commented:
It's a datetime datatype.
0
 
ZberteocCommented:
show me the statement you used, please.
0
 
Scott PletcherSenior 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
 
ZberteocCommented:
Ok, I made a slight modiffication:

SELECT
      substring(convert(varchar(23),dateadd(ms,avg(cast(datediff(ms,DATEADD(dd,DATEDIFF(dd,0,date_column),0),date_column) AS bigint)),getdate()),121),12,12) AS AverageHour
FROM
      YourTable

and I used it against a table with 4 millions rows and worked.
0
 
ZberteocCommented:
Sorry, marcus, here is the statement that will give you the right answer:

SELECT TOP 10
      substring(convert(varchar(23),dateadd(ms,avg(cast(datediff(ms,DATEADD(dd,DATEDIFF(dd,0,date_column),0),date_column) AS bigint)),DATEADD(dd,DATEDIFF(dd,0,getdate()),0)),121),12,12) AS AverageHour
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
 
ZberteocCommented:
ignore the TOP 10, i used it in my tests.
0
 
marcus72Author 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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