September 29, 2009 at 12:28 pm
I'm working on a table containing a column defined as bigint. The value is actually a time value but I'm having issues trying to convert it. I queried the data source and can see what the time value is using a test table with a datetime datatype. I don't care about the date itself, only the time.
Example.
bigint value: 519810000000
datetime value: 2009-09-28 01:15:38.000
I would like to convert 519810000000 into 01:15:38.000.
I'm using a formula I found online but it doesn't give me the correct result which means my parameters are probably not correct.
DECLARE @dt AS bigint
SET @dt = 519810000000
SELECT dateADD(ms, (@dt / CAST(10000 AS bigint)) % 86400000,
DATEADD(hour, @dt / CAST(864000000000 AS bigint) - 109207, 0))
Can anyone help tweak this formula so the bigint can be converted into the time?
September 29, 2009 at 12:38 pm
Question - what does 519810000000 represent? In other words, how do you know what the correct answer is in terms of that datetime you have in your question?
Is that number of seconds since Year 0, etc?
MJM
September 29, 2009 at 12:49 pm
Unfortunately I don't know what the 519810000000 represents. The time value for this number is 01:15:38.000. I queried the data source for the same account to get the actual time value.
September 29, 2009 at 12:55 pm
Hmmm....okay. Do you have any other examples (the integer numbers and the dates they correspond to)?
MJM
September 29, 2009 at 12:56 pm
If you post 5 or 6 number/datetime pairs, there is a good chance someone could figure it out. Numbers over a large range of time would be best.
September 29, 2009 at 2:22 pm
OK, here are some more examples. I really appreciate your help!
BIGINT Value TIME Value
80100000000:13
323500000000:53
453800000001:15
459800000001:16
579600000001:36
660400000001:50
795200000002:12
937000000002:36
961600000002:40
1006700000002:47
1087400000003:01
1814800000005:02
40544000000011:14
48623000000013:30
58680000000016:18
September 29, 2009 at 2:45 pm
Got it - thanks to PESO whose post I found out elsewhere on the Interwebs. It's number of seconds since UNIX epoch, just in ten millionths of a second. Hope that makes sense.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=108906
Here's how you can test it:
CREATE TABLE dbo.TimeValues(
TimeInt BIGINT)
INSERT INTO dbo.TimeValues(TimeInt) VALUES(8010000000)-- 0:13
INSERT INTO dbo.TimeValues(TimeInt) VALUES(32350000000)-- 0:53
INSERT INTO dbo.TimeValues(TimeInt) VALUES(45380000000)-- 1:15
INSERT INTO dbo.TimeValues(TimeInt) VALUES(45980000000)-- 1:16
INSERT INTO dbo.TimeValues(TimeInt) VALUES(57960000000)-- 1:36
INSERT INTO dbo.TimeValues(TimeInt) VALUES(66040000000)-- 1:50
INSERT INTO dbo.TimeValues(TimeInt) VALUES(79520000000)-- 2:12
INSERT INTO dbo.TimeValues(TimeInt) VALUES(93700000000)-- 2:36
INSERT INTO dbo.TimeValues(TimeInt) VALUES(96160000000)-- 2:40
INSERT INTO dbo.TimeValues(TimeInt) VALUES(100670000000)-- 2:47
INSERT INTO dbo.TimeValues(TimeInt) VALUES(108740000000)-- 3:01
INSERT INTO dbo.TimeValues(TimeInt) VALUES(181480000000)-- 5:02
INSERT INTO dbo.TimeValues(TimeInt) VALUES(405440000000)-- 11:14
INSERT INTO dbo.TimeValues(TimeInt) VALUES(486230000000)-- 13:30
INSERT INTO dbo.TimeValues(TimeInt) VALUES(586800000000)
SELECT
DATEADD(SECOND, tv.TimeInt / 10000000, '19700101 00:00') AS FullDateTime,
CONVERT(VARCHAR(12),
DATEADD(SECOND, tv.TimeInt / 10000000, '19700101 00:00'), 114) AS TimeOnly
FROM dbo.TimeValues tv
MJM
September 29, 2009 at 2:53 pm
Mark,
Thanks Dude! I ran the scripts and it seems to do the job. I'll work on applying it to the table I'm working with. The business customers will be happy!
June 22, 2010 at 6:50 am
Like this how can i convert 131401986 to 02/09/2005
June 22, 2010 at 7:19 am
shanila_minnu (6/22/2010)
Like this how can i convert 131401986 to 02/09/2005
Should've posted your question in a new thread. Regardless. . . something like this?
--DECLARE AND SET VARIABLE
DECLARE @number AS BIGINT
SET @number = 131401986
--Query
DECLARE @temp AS VARCHAR(10)
SET @temp = CAST(CAST(Substring(( CAST(@number AS BINARY(4)) ), 4, 1) AS INT) AS
VARCHAR) +
'/' + CAST(CAST(Substring(( CAST(@number AS BINARY(4)) )
, 3, 1) AS
INT) AS VARCHAR) + '/' +
CAST(CAST(Substring(( CAST(@number AS BINARY(4)) ), 1, 2) AS INT) AS
VARCHAR)
SELECT @temp,
CAST(@temp AS DATETIME)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply