March 5, 2015 at 12:40 pm
I have a column in a docave table (timecreated) that is typed bigint.
I cannot for the life of me figure out how to convert this into a sensible datetime value.
there will be a prize for anyone who helps me out of the extremely annoying situation
thank you!
March 5, 2015 at 1:30 pm
snomadj (3/5/2015)
I have a column in a docave table (timecreated) that is typed bigint.I cannot for the life of me figure out how to convert this into a sensible datetime value.
there will be a prize for anyone who helps me out of the extremely annoying situation
thank you!
It is a heck of a challenge you have offered here. We have no idea what your date values look like. Without some details nobody can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 5, 2015 at 1:34 pm
snomadj (3/5/2015)
I have a column in a docave table (timecreated) that is typed bigint.I cannot for the life of me figure out how to convert this into a sensible datetime value.
there will be a prize for anyone who helps me out of the extremely annoying situation
thank you!
A likely conversion is that bigint column represents the time in milliseconds since 1970-00-00 00:00:00.000.
This is a common format for applications that are Java or UNIX based. Also, note that the time may be in UTC time, so it may be offset from your current time zone.
If you can identify rows in your table that are recently created, you can try the conversion using the code below.
-- Convert from bigint to datetime
select
a.MyBigintTime,
MyDateTime =
dateadd(ms,a.MyBigintTime%86400000,(a.MyBigintTime/86400000)+25567)
from
( --Test Data
select MyBigintTime = 1425569207060
) a
Results:
MyBigintTime MyDateTime
--------------------------------------- -----------------------
1425569207060 2015-03-06 06:53:34.117
-- convert from datetime to bigint
select
a.MyDateTime ,
MyBigintTime =
(datediff(dd,25567,a.MyDateTime)*00000086400000)+
datediff(ms,dateadd(dd,datediff(dd,0,a.MyDateTime),0),a.MyDateTime)
from
( -- Test Data
select MyDateTime = getdate()
) a
Results:
MyDateTime MyBigintTime
----------------------- ---------------------------------------
2015-03-05 15:32:11.933 1425569531933
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply