-1

I have a time field which is storing numbers in '49235062'. Can these be converted to an actual readable time?

This does not look anything like a time stamp.

Thanks

user3306489
  • 159
  • 1
  • 9
  • 2
    Where is the number coming from. What makes you think that it is a timestamp? – Peter Smith Sep 26 '19 at 16:25
  • 1
    If you consider this number as a unix timestamp (seconds since Jan 01 1970. (UTC)), it _could_ correspond to 07/24/1971 @ 8:24pm (UTC) – Pac0 Sep 26 '19 at 16:28
  • There's a possible answer [here](https://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server) if it is a unix timestamp. – Peter Smith Sep 26 '19 at 16:31
  • @peter the field name is called CRT TIME and is stored as a varchar. The CRT DATE does not show the time. – user3306489 Sep 26 '19 at 16:52
  • @pac0 tried the unix time conversion but it returned error saying; – user3306489 Sep 26 '19 at 16:54
  • @Pac0 Spot on with UNIX timestamp – Peter Smith Sep 26 '19 at 17:39
  • @PeterSmith actually, if the field is only labelled "time", I'm betting on [John Cappelletti's answer](https://stackoverflow.com/a/58121239/479251) – Pac0 Sep 26 '19 at 21:12
  • Without solid evidence it is impossible to tell which is correct; the name of a field is not enough. Do you have any external supporting evidence? I come back to my original comment: what is the source/origin of the number? – Peter Smith Sep 27 '19 at 06:52
  • Nope, I don't have evidence, that's why I used the verb "bet" ;) .The question indeed lacks context / additional information. However, the *hint* that makes me bet in favor of John's answer instead of my original hypothesis, is that the unix timestamp would imply also a change of several days, or even months, to get only a time at the end. That _looks_ unprobable to me. But this just an opinion, maybe completely wrong. – Pac0 Sep 27 '19 at 16:10

2 Answers2

5

Just a guess, but is this milliseconds from midnight?

Example

Select dateadd(MILLISECOND,49235062,0)

Returns

1900-01-01 13:40:35.063   -- 1:40 PM

If so, then it is a small matter to convert to time or format as time

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • 1
    Perfect, thanks. The time marries with the front end application. If I add in the column, an error Argument data type varchar is invalid for argument 2 of dateadd function. How do I correct this? – user3306489 Sep 27 '19 at 10:16
  • 1
    Amended to format(dateadd(MILLISECOND,cast(c.[CRT-TIME] as int),0), 'HH:mm:ss') [CRT-TIME] – user3306489 Sep 27 '19 at 11:23
0

Assuming that this is a UNIX timestamp (number of seconds since 1/1/1970), try the following:

DECLARE @timeStamp varchar(10) = '49235062'

SELECT @timeStamp, CONVERT(TIME, dateadd(S, CAST(@timeStamp AS int), '1970-01-01'))

Produces the following

49235062    20:24:22.0000000

So your time is 20:24:22 which is the answer that @pac0 suggested.

Peter Smith
  • 5,528
  • 8
  • 51
  • 77