从日期时间转换为 INT [英] Convert from DateTime to INT

问题描述

在我的 SSIS 包中，我必须将值从 DateTime 转换为相应的 INTEGER 值.已提供以下示例.

In my SSIS package, I have to converting values from DateTime to a corresponding INTEGER value. The following sample has been provided.

关于如何转换这些有什么想法吗?

Any ideas as to how I can convert these?

DATETIME   INT
---------  ----
1/1/2009   39814
2/1/2009   39845
3/1/2009   39873
4/1/2009   39904
5/1/2009   39934
6/1/2009   39965
7/1/2009   39995
8/1/2009   40026
9/1/2009   40057
10/1/2009  40087
11/1/2009  40118
12/1/2009  40148
1/1/2010   40179
2/1/2010   40210
3/1/2010   40238
4/1/2010   40269
5/1/2010   40299
6/1/2010   40330

推荐答案

转换为 float/int 在 SQL Server 的最新版本中不再有效.请改用以下内容:

Casting to a float/int no longer works in recent versions of SQL Server. Use the following instead:

select datediff(day, '1899-12-30T00:00:00', my_date_field)
from mytable

注意字符串日期应该是明确的日期格式，这样它就不是't 受服务器区域设置的影响.

Note the string date should be in an unambiguous date format so that it isn't affected by your server's regional settings.

在旧版本的 SQL Server 中，您可以通过先转换为浮点数，然后转换为整数来将日期时间转换为整数:

In older versions of SQL Server, you can convert from a DateTime to an Integer by casting to a float, then to an int:

select cast(cast(my_date_field as float) as int)
from mytable

(注意:您不能直接转换为 int，因为如果您过了中午，MSSQL 会将值向上舍入！)

(NB: You can't cast straight to an int, as MSSQL rounds the value up if you're past mid day!)

如果您的数据中有偏移，您显然可以从结果中添加或减去它

If there's an offset in your data, you can obviously add or subtract this from the result

您可以通过直接向后投射来向另一个方向转换:

You can convert in the other direction, by casting straight back:

select cast(my_integer_date as datetime)
from mytable

这篇关于从日期时间转换为 INT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！