Excel 日期格式与数字格式转换的BUG

最新推荐文章于 2018-10-12 10:30:57 发布
最新推荐文章于 2018-10-12 10:30:57 发布
阅读量385 收藏
点赞数
文章标签： Excel SQL Server SQL 算法

Excel 2003 中的单元格式可以有多种, 如下图:



其中日期/时间可以与数字进行互转, 但前提是这些日期/时间必须是Excel能识别的格式, 例如: 输入2009-12-24 , 转换为数字是:40171

输入2009-12-24 10:10:10, 转换为数字是40171., 官方说法其日期是以1900年1月1日为起始日, 时间以午夜零时为起始时间.



MS SQL Server 2005 的datetime类型的日期时间也是以1900年1月1日零时作为起始点, 我想, 当我们以这个数值(40171.4237268519)插入MS SQL Server 2005 字段为datetime 类型的表时, 产生的日期应该是: 2009-12-24 10:10:10 ,


但很可惜, 实际上得到的却是:




比预期的多出了两天!!! 为什么?

同是微软的东西, 难道算法还会不一致?


原来这里面有一个很隐蔽的BUG(不知道算不算BUG):


先在Excel的某个单元格上设置格式为日期格式, 然后输入0, 回车! 结果是1900-1-0 , 晕了原来Excel的参考起始日期并不是官方说的1900-1-1 , 而是1900-1-0 , 这样算出来的日期就比如SQL Server算出来的多出了一天!

 

再次在单元格上输入60 , 然后回车, 结果是1900-2-29 ! 微软真能搞, 1900年并不是润年(真正的判断法则是：能被4或400整除,但不能被100整除的年份为闰年), 所以这里又多出了一天!

 

所以Excel算出来的日期数值与SQL Server算出来的日期数值相差2 天!

susam6854
关注
Excel2Json进阶版
09-21
"Excel2Json进阶版"是一个Python工具，它专为将Excel文件转换成Json格式而设计，适用于那些需要在Excel和Json之间灵活切换的项目。这个工具不仅提供基本的转换功能，还支持Json的全格式，包括Map（字典）和数组等...
Excel日期显示为数字,不能正常显示为日期
最新发布
feiyu208的专栏
07-02 664
表格使用过程中，之前写的日期现在都是数字了，更改了单元格 格式，不起作用，双击编辑的情况下可以看到日期，网上查了一下，后来解决了,这里记录一下。原文链接：https://blog.csdn.net/fanbaodan/article/details/103636324。可以看到显示的都是数字,格式确实是日期的格式, 网上查了一番, 有什么选择粘贴格式的,数据分列的,统统没有解决问题.其实很简单,只显示格式的问题,以为你的工作簿处于公式审核状态,具体什么是公式审核状态这里就不说了,有兴趣的自行研究.
java时间格式的转换以及EXCEL里面时间的读取
shunzi2016的博客
02-08 2万+
一、/** * @author SHUN * 生成系统日期，并将其格式化返回字符串 * @return */ public String generateDate(){ String guarantee_date = null; Date generateDate = new Date(); DateFormat formater =...
Excel表格取到的时间，在SQLServer中，格式化显示
小圣_fighting的博客
02-17 1294
CAST函数，-2 是在测试中，Excel时间格式化过来后，总会和真实时间差2天。  CAST((DATEADD(DAY,CAST(SaleDate AS INT)-2 ,'1900-01-01')) AS DATE) SaleDate
Excel百宝箱8.0
06-07
函数功能与参数：与“大写”函数相反，将大写字符转换为阿拉伯数字。 函数名称：分割取数 函数功能与参数：按分割符取数。第一参数为单元格，第二参数表示取第N个，第三个参数表示分割符。 函数名称：共有项 函数...
EXCEL集成工具箱V8.0完整增强版(精简)
09-23
【数字转英文】 将选中区域（默认）或已使用区域存储格的金额数字转换成英文格式显示的表达字符串，为三资企业制作客户外汇付款通知书的理想工具。 【文本转EXCEL】 将文本文件按指定的分隔符号分隔一次性导入到...
PHPDBTabletoExcel:直接从数据库表行创建Excel工作表
03-27
5. **数据格式化**：在生成Excel的过程中，我们还可以对数据进行格式化，比如设置数字格式、日期格式，甚至应用样式如粗体、颜色等。 6. **文件保存**：最后，将生成的Excel工作表保存为一个`.xlsx`文件，用户可以...
NPO I2.0.1与2.1.3.1.rar
08-01
2. **数据类型**：NPOI支持多种数据类型，如字符串、数字、日期、布尔值等，可以方便地将数据转换为合适的单元格类型。 3. **样式和格式**：开发者可以通过NPOI设置单元格的样式，包括字体、颜色、边框、对齐方式、...
解决excel日期变成数字的问题
weixin_34409822的博客
08-01 3989
在Excel中如果单元格的公式是日期格式，那么引用后的数值是错误的[不是日期格式而被转换成数字类型了],这种情况显然不是我们想要的结果    解决办法：在公式中强制转成文本类型即可(="Date:"&TEXT(A2,"YYYY-M-D")&",")参考：案例呈现excel日期变成数字的原因和解决方法在Excel 里如何把各种非标准日期格式转换标准日期格式microsoft:如何使用...
打开Excel，原有的数字内容都自动转换成日期格式的问题（亲自实践）
热门推荐
bigheadsheep的专栏
08-04 2万+
不知道什么原因，原有Excel中的数字内容，从某天开始打开之后，都自动变成了日期格式 调整起来很烦，而且调整后保存关闭，再打开，又自动变为日期格式 从网上找了找方法，试了一下，问题解决！ 1. 打开Excel，任意选中一单元格，单击鼠标右键，选择设置单元格格式。 2. 在数字自定义类型中，找到前缀为[$-F400]的类型格式（或者类似的比如[$-F800]），点击删除。 3. 保
EXCEL日期时间格式读取为数字问题
hanwen112的博客
10-12 3848
1、excel的时间设置为常规就会转为数字，数字与1900年1月1日计算差值 日期1 常规1 日期2 常规2 2018/10/12 43385 1900/1/1 1   2、excel的1900年是闰年，2月有29日；数据库等其他记录为2月有28天 3、oracle计算转换 ...
Java 将Excel中时间数字格式转为指定格式
qq_33327680的博客
09-21 5011
   在编程过程中经常会遇到时间格式转换的问题，而Excel表格中的时间转换方式不太一样，下面就是转换的代码，再次记录一下 public static Date getDate(int days) { Calendar c = Calendar.getInstance(); c.set(1900, 0, 1); c.add(Calendar.DATE, days - 2); r...
kettle处理日期格式转换
09-07
在Kettle中处理日期格式转换的步骤如下： 1. 首先，您需要使用Java的SimpleDateFormat类来定义您想要的日期格式。您可以使用类似于以下代码的方式创建一个SimpleDateFormat对象： ```java java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat(dateFormat); ``` 2. 接下来，您需要使用转换函数来将日期字符串转换为特定格式的日期对象。在您的代码中，使用的是JavaScript代码，您可以将其转换为Kettle的JavaScript脚本步骤。 您可以使用类似于以下的代码来定义一个转换函数： ```javascript function trans(time) { var str = time; var a = str.split("T").join(" "); var b = a.substring(0, 19); var c = b.replace(/-/g, "/"); var date = new Date(c); return date.getTime(); } ``` 3. 然后，您可以使用Kettle的JavaScript脚本步骤中的转换函数来将日期字符串转换为特定格式的日期对象。在您的代码中，您可以像这样使用转换函数： ```javascript var publishTime = trans("2022-04-15T00:00:00 08:00"); var validDateStart = trans("2022-04-15T00:00:00 08:00"); var validDateEnd = trans("2022-04-15T00:00:00 08:00"); ``` 4. 最后，您可以将转换后的日期对象用于Kettle中的日期处理。根据您的需求，您可以将日期对象传递给Kettle的相关组件或步骤进行后续处理。 综上所述，要在Kettle中处理日期格式转换，您需要使用SimpleDateFormat类定义日期格式，编写转换函数将日期字符串转换为日期对象，然后在Kettle中使用转换后的日期对象进行处理。请注意，这是一种示例方法，您可以根据您的具体需求进行适当的修改和调整。<span class="em">1</span><span class="em">2</span><span class="em">3</span><span class="em">4</span> #### 引用[.reference_title] - *1* *3* *4* [（BUG）Kettle8.1.0.0-365转换步骤输出SQL文件insert脚本日期类型数据，格式不正确且没有双引号](https://blog.csdn.net/weixin_28953369/article/details/114307563)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] - *2* [kettle中关于js处理Date日期数据，将日期格式改为时间戳](https://blog.csdn.net/weixin_44047573/article/details/124252448)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值