how to convert bigint to datetime in sql server

Question

1.00/5 (1 vote)

See more:

C#

Hello,

I have a value in bigint and i need to convert it into datetime my value is this "381403800" i tried these solutions but not a single solution is working... its correspond value is '1982-02-01 09:30:00.000'.

SQL

SELECT DATEADD(SECOND, 381403800 / 1000, '19691231 20:00')

select dateadd(hh,-4,dateadd(ss,381403800/1000,'1970/01/01 00:00'))

select CONVERT(DATETIME,REPLACE(DATEADD( day, 381403800, '12/31/1849'),'12/31/1849',0),101)

SELECT DateAdd(ms, 381403800 % 1000, DateAdd(SECOND, 381403800 /  1000, '1970-01-01T00:00:00.000')) AS ToTheMs,
   DateAdd(SECOND, 381403800 /  1000, '1970-01-01T00:00:00.000')  AS ToTheSecond,
   DateAdd(MINUTE, 381403800 / 60000, '1970-01-01T00:00:00.000')  AS ToTheSecond

Posted 25-Sep-14 19:25pm

srishti_

Updated 25-Sep-14 19:26pm

v2

Add a Solution

Comments

george4986 26-Sep-14 1:34am

can u plz tell me how "381403800" is related to "1982-02-01 09:30:00.000" ?

srishti_ 26-Sep-14 2:32am

i don't know i have a XML file and corresponding HTML showing this value.

george4986 26-Sep-14 2:59am

can u post the xml structure and ur html conversion code here?

srishti_ 26-Sep-14 3:29am

I am not converting XML to HTML its a government file both XML and their corresponding HTML .I am only insert that XML data to my database for that i need that big int conversion to date time that's it

george4986 26-Sep-14 4:39am

what about other values ,does they also differ in xml and html?
are u sure no encryption is used in xml?

srishti_ 26-Sep-14 4:54am

ya there is no encryption

syed shanu 26-Sep-14 1:43am

hi Check this Link
http://stackoverflow.com/questions/19237731/displaying-sql-column-in-date-format-from-bigint

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/b4b8cba3-f4c8-40d7-960a-ce13467f09a8/convert-bigint-timestamp-to-a-datetime?forum=transactsql

select dateadd(second, 381403800 /1000 + 8*60*60, '19820201')

select dateadd(second,
381403800 /1000 + 381403800 % 1000 + 8*60*60, '19820201')

SELECT CAST(SWITCHOFFSET(CAST(DATEADD(SECOND,381403800 /1000 + 381403800
% 1000 + 8*60*60, '19820201') AS DATETIMEOFFSET),'-05:00') AS DATETIME2)

select
dateadd(second, 381403800/1000+8*60*60, '19820201')
, convert(varchar, dateadd(second, 381403800/1000+8*60*60, '19820201') ,121)

srishti_ 26-Sep-14 2:31am

no its not working result is not correct it showing value '1982-02-05 17:56:43.000' ,'1982-02-05 18:10:03.000' ,'1982-02-05 13:10:03.0000000' ,'1982-02-05 17:56:43.000' while the correct value is this '1982-02-01 15:00:00.000'

Sergey Alexandrovich Kryukov 26-Sep-14 3:29am

Why would you ever use BigInteger to represent time? :-)
—SA

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Thomas Nielsen - getCore · Answer 1 · 2014-09-25T21:03:00

Cool problem!
Fire this up in your SQL manager, lore and behold

select convert(datetime, 0)
 select convert(bigint, convert(datetime, '19820201'))
 -- 29981 equivalates to 1982-02-01 NOT 381403800
 select convert(bigint, getdate())
 -- today is 41906
 select convert(bigint,dateadd(day, 1, getdate()))
 -- tomorrow is 41907
 declare @later int;
 set @later = 381403800 - convert(bigint, getdate())
 --select dateadd(day, @later, getdate()) would get overflow on the date type
 declare @oneTenth int;
 set @oneTenth = @later / 1000
 select dateadd(day, @oneTenth, getdate())
 -- so 1/1000 of the date you're reqesting is 3058-11-13
 -- which is 
 select datediff(day, getdate(), convert(datetime, '3058-11-13'))
 -- 381361 days from now or in rougly 1044 years, adding the zeroes you get 1.044.000 years
 --so in a million years all that we know is considered to be over, including mssql?
 -- your date is year 1046014 bc :)

wonder where you got that date from :)

in dot net you would get the date from that tick of 01-01-0001 00:00:38
this is intreguingly close to the dotnet value DateTime.Min (01-01-0001 00:00:00)

C#

var dt = DateTime.MinValue.AddSeconds(38); //new DateTime(381403800);
            System.Diagnostics.Debug.WriteLine(dt.Ticks);

you get a value almost equal to the one you have

What you got to do is to check if the date being converted is below the minimum value the sql server can hold and then set it to null or the minimally supported value, see the first convert