convert bigint to datetime in sql

Question

1.00/5 (1 vote)

See more:

Hii,

I have a database in c tree ...
All data of this database is available in excel in this all fields related to date or date-time is stored in big int data type i want to merge this data in my database which is in mssql so i need to convert this bigint format to datetime format as per my database... How can i convert this to datetime please help

Some bigint date examples are..
58317
58318

For Help
Convert “Date” to CDate
The CDate (number) is a value representing the number of days starting from December 30,
1899. The date can be positive or negative, and is truncated if fractional. There are 18261 days
between 01/01/1850 and 12/30/1899 and then another 18263 days to 12/31/1950. The long
integer dates would be CDate (value-18261) and the short integer dates would be CDate
(value+18263). For example, for the MRSC99 table the CDate would be
(MRSC99.D_Chart_Open –18261) and for the MRMI99 table the CDate would be
(MRMI99.D_Mrmi +18263).

Thanks & regards
Srishti Gupta

Posted 22-Jun-14 20:12pm

srishti_

Updated 22-Jun-14 20:23pm

v2

Add a Solution

Comments

Kornfeld Eliyahu Peter 23-Jun-14 2:17am

Do you know what date those number represent?

srishti_ 23-Jun-14 2:19am

Sorry i don't know..

Kornfeld Eliyahu Peter 23-Jun-14 2:21am

In that case it will be hard to tell how to convert it - you will have to wait until someone smart will spot the numbers' meaning...

srishti_ 23-Jun-14 2:24am

Please read again my question it has some improvement

2 solutions

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)

Abhinav S · Answer 1 · 2014-06-22T20:22:00

Solution 1

Try select dateadd(second, 1334034120644 /1000 + 8*60*60, '19700101')

Posted 22-Jun-14 20:22pm

Abhinav S

Comments

srishti_ 23-Jun-14 2:33am

Plesae explain where i put my value

Sergey Vaselenko · Answer 2 · 2014-06-22T21:58:00

Microsoft Excel stores date values in OLE Automation date format.

DateTime.ToOADate[^]
DateTime.FromOADate[^]

But in your case, 58317 is 1959-08-30. If to add 18263, this is 2009-08-31.

Is one of these dates correct?

In any case, you may merge data from Excel to a database using Data Merge Wizard[^] of the SaveToDB add-in.

You may prepare data to merge using Excel formulas. So you may prepare correct dates if you know the converting rule.