Hii, 我在c树中有一个数据库... 这个数据库的所有数据都可以在excel中找到所有与日期或日期时间相关的字段都存储在big int数据类型中我希望将这些数据合并到我的数据库中,这是在mssql中所以我需要根据我的数据库将这个bigint格式转换为datetime格式...我怎么能将此转换为日期时间请帮助 一些bigint日期示例是.. 58317 58318 寻求帮助 将Date转换为CDate CDate(数字)是一个值,表示从12月30日开始的天数, 1899.日期可以是正数或负数,如果是正数或负数,则截断日期分数。从18/01年1月1日到12月30日到1899年之间共有18261天 ,然后是另一个18263天到20/31/1950。长的 整数日期将是CDate(值-18261),短整数日期将是CDate (值+ 18263)。例如,对于MRSC99表,CDate将是 (MRSC99.D_Chart_Open -18261),对于MRMI99表,CDate将是 (MRMI99.D_Mrmi +18263) 。 谢谢&问候 Srishti Gupta
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
推荐答案尝试选择dateadd(第二,1334034120644 / 1000 + 8 * 60 * 60,'19700101')
Microsoft Excel以OLE自动化日期格式存储日期值。 DateTime.ToOADate [ ^ ] DateTime.FromOADate [ ^ ] 但在你的情况下,58317是1959-08-30。如果要添加18263,这是2009-08-31。 其中一个日期是否正确? 在任何情况下,您都可以使用数据合并向导 [ ^ SaveToDB加载项。 您可以使用Excel公式准备要合并的数据。因此,如果您知道转换规则,则可以准备正确的日期。 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.
更多推荐
在sql中将bigint转换为datetime
发布评论