none
Datetime中的时间问题 RRS feed

  • 问题

  • 环境:windows server 2003 R2 EN+SP2、SQL 2005+SP4 CN

    情况:用SSIS从txt平面文件ETL到SQL数据库。原始文件有字段(sourcedate)格式为“2012-11-25 16:05:11”,SQL中目标字段(destinationdate)格式为datetime数据类型。数据传送完成后,发现在destinationdate中数据显示为“2012-11-25 16:05:11 PM”,这个明显是存在问题的。

    问题:

    1、不重新ETL的前提下(数据量太大),如何更正destinationdate中的数据格式?

    2、如何正确地ETL?上述情况是否是英文版操作系统本地日期格式设置所造成的?

    谢谢!

    2012年11月27日 2:02

答案

  • 发现在destinationdate中数据显示为“2012-11-25 16:05:11 PM”,这个明显是存在问题的。

    这个应该是显示问题, 显示的格式取决于你的客户端工具设置(未显式设置格式的话, 跟你客户端系统的控制面板--区域设置中的日期/时间格式有关)

    • 已标记为答案 John_WANG 2012年11月28日 2:15
    2012年11月27日 3:28
  • 但是还有个问题:我用between and 查询时,如果用 select * from table1 where date between '2012-11-20' and '2012-11-25' ,结果显示的却是 2012-11-20 到 2012-11-24 日的数据。不知为何会少一天?

    你好,

    当你没有指定2012-11-25 的 时间部分数据时, 它默认是到12:00 AM 结束, 所以,如果你的记录时间是在2012-11-25 12:00 AM 以后的话,就不会返回那条数据了。 请查看下你的数据是不是这种情况。

    http://msdn.microsoft.com/en-us/library/ms187922.aspx

    Best Regards,


    Description: Description: TechNet 论坛好帮手立刻免费下载TechNet论坛好帮手

    2012年11月28日 2:42
    版主

全部回复

  • 你这个明显是经验不足

    通常etl过程都是将原始数据按照原始格式加载到数据库(意思就是原来是文本,所有字段都文本加载到数据库表)然后在做类型处理,这样可以避免加载过程中意外(不能转换的类型,错误的类型转换等等)

    当然你已经加载了,只能通过脚本将类型转换你需要的了,很简单,将正确的格式写入到临时表,然后清空原来表,到将临时表写回去即可。(你这里数据量大不推荐update)


    family as water


    • 已编辑 Stone Z 2012年11月27日 2:25
    2012年11月27日 2:24
  • 你这个明显是经验不足

    通常etl过程都是将原始数据按照原始格式加载到数据库(意思就是原来是文本,所有字段都文本加载到数据库表)然后在做类型处理,这样可以避免加载过程中意外(不能转换的类型,错误的类型转换等等)

    当然你已经加载了,只能通过脚本将类型转换你需要的了,很简单,将正确的格式写入到临时表,然后清空原来表,到将临时表写回去即可。(你这里数据量大不推荐update)


    family as water


    在ETL时转换类型,主要有两个原因:

    1、存储空间问题。如果用原始的nchar格式会占用大量磁盘空间。

    2、聚合数据效率问题。如果将数据格式转换放到聚合数据时执行,会降低执行效率。

    对于我这个具体的问题:将“2012-11-25 16:05:11 PM”转换为“2012-11-25 4:05:11 PM”,有什么具体、高效的方法么?

    谢谢!

    2012年11月27日 2:58
  • You can convert datetime with proper style, books online has details under 'cast and convert (transact-sql)'.
    2012年11月27日 3:06
  • 发现在destinationdate中数据显示为“2012-11-25 16:05:11 PM”,这个明显是存在问题的。

    这个应该是显示问题, 显示的格式取决于你的客户端工具设置(未显式设置格式的话, 跟你客户端系统的控制面板--区域设置中的日期/时间格式有关)

    • 已标记为答案 John_WANG 2012年11月28日 2:15
    2012年11月27日 3:28
  • 你可以使用 convert(varchar, 日期字段, 121) 这种方式, 显式地转换一下日期格式来看看结果
    2012年11月27日 3:30
  • 你这个明显是经验不足

    通常etl过程都是将原始数据按照原始格式加载到数据库(意思就是原来是文本,所有字段都文本加载到数据库表)然后在做类型处理,这样可以避免加载过程中意外(不能转换的类型,错误的类型转换等等)

    当然你已经加载了,只能通过脚本将类型转换你需要的了,很简单,将正确的格式写入到临时表,然后清空原来表,到将临时表写回去即可。(你这里数据量大不推荐update)


    family as water



    这个不叫etl,叫elt。etl中间的t就是处理各种意外情况的。加载到数据库中转换效率太低。

    想不想时已是想,不如不想都不想。

    2012年11月27日 7:17
    版主
  • 如果目标格式确实是datetime,那么数据是没有问题的,只是显示的问题。

    想不想时已是想,不如不想都不想。

    2012年11月27日 7:19
    版主
  • 发现在destinationdate中数据显示为“2012-11-25 16:05:11 PM”,这个明显是存在问题的。

    这个应该是显示问题, 显示的格式取决于你的客户端工具设置(未显式设置格式的话, 跟你客户端系统的控制面板--区域设置中的日期/时间格式有关)

    “2012-11-25 16:05:11 PM”,AM和PM的范围应该都是 00:00:00:001到12:00:00:000,不存在 16:05:11 PM 。

    2012年11月27日 9:32
  • 如果目标格式确实是datetime,那么数据是没有问题的,只是显示的问题。

    想不想时已是想,不如不想都不想。


    “2012-11-25 16:05:11 PM”,AM和PM的范围应该都是 00:00:00:001到12:00:00:000,不存在 16:05:11 PM 。

    2012年11月27日 9:33
  • 纠正:AM和PM的范围应该是 01:00:00到12:59:59
    2012年11月27日 9:42
  • 值与显示格式是蒙了不少人,呵

    也可能EXCEL里字体大小、颜色的格式更直观点,就清晰点


    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com

    2012年11月27日 10:39
  • 发现在destinationdate中数据显示为“2012-11-25 16:05:11 PM”,这个明显是存在问题的。

    这个应该是显示问题, 显示的格式取决于你的客户端工具设置(未显式设置格式的话, 跟你客户端系统的控制面板--区域设置中的日期/时间格式有关)

    我重设了区域设置中的日期/时间,现在显示正常了。

    但是还有个问题:我用between and 查询时,如果用 select * from table1 where date between '2012-11-20' and '2012-11-25' ,结果显示的却是 2012-11-20 到 2012-11-24 日的数据。不知为何会少一天?

    2012年11月28日 1:33
  • 但是还有个问题:我用between and 查询时,如果用 select * from table1 where date between '2012-11-20' and '2012-11-25' ,结果显示的却是 2012-11-20 到 2012-11-24 日的数据。不知为何会少一天?

    你好,

    当你没有指定2012-11-25 的 时间部分数据时, 它默认是到12:00 AM 结束, 所以,如果你的记录时间是在2012-11-25 12:00 AM 以后的话,就不会返回那条数据了。 请查看下你的数据是不是这种情况。

    http://msdn.microsoft.com/en-us/library/ms187922.aspx

    Best Regards,


    Description: Description: TechNet 论坛好帮手立刻免费下载TechNet论坛好帮手

    2012年11月28日 2:42
    版主
  • 但是还有个问题:我用between and 查询时,如果用 select * from table1 where date between '2012-11-20' and '2012-11-25' ,结果显示的却是 2012-11-20 到 2012-11-24 日的数据。不知为何会少一天?

    你好,

    当你没有指定2012-11-25 的 时间部分数据时, 它默认是到12:00 AM 结束, 所以,如果你的记录时间是在2012-11-25 12:00 AM 以后的话,就不会返回那条数据了。 请查看下你的数据是不是这种情况。

    http://msdn.microsoft.com/en-us/library/ms187922.aspx

    Best Regards,


    Description: Description: TechNet 论坛好帮手立刻免费下载TechNet论坛好帮手


    确实是这个原因,多谢。 只要在between参数中加上时间就可以了。
    2012年11月30日 8:19