数据库时间 用bigint,在sql中将bigint转换为datetime

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

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

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.