如何将SQL datetime2转换为double？ [英] How to cast sql datetime2 to double?

问题描述

我在SQL Server 2016中有一个表，该表的列名为datetime2（0）的开始。在我从该表中提取数据的应用程序中，我需要将该列数据转换为双精度型（我正在使用要求数据为双精度数组的第三方应用程序）。我试图以这种方式将数据转换为字符串以将其传递给我的应用程序：

  SELECT Cast（启动为AS CHAR（19））从MyTable 
  

然后将应用程序中的字符串转换为double。这种方法行不通，因为我无法将字符串转换为双精度型。有什么好的方法？

解决方案

按两倍，我假设您的意思是浮点数。

datetime2（0）可以首先转换为日期时间（不损失精度），该转换很容易浮动

假设您在1753年之前没有值

 从MyTable 
中选择CAST（CAST（开始日期时间开始）AS浮动） > 
 
 
示例
  DECLARE @foo datetime2（0）= GETDATE（）
选择@foo，CAST（CAST（@foo AS datetime）AS float）
 
-2018-09-20 11:31:32 43361.4802314815 
 GO 
 
 DECLARE @foo datetime2（0）= GETDATE（）
 SELECT @foo，CAST（@foo AS float）--error 
 
 --Msg 529，Level 16，State 2，第5行
-不允许从数据类型datetime2到float的显式转换。 
  
 
I have a table in SQL Server 2016 with a column named "Start" of type datetime2(0).  In my application that pulls data from that table, I need to convert that columnar data to a double (I'm using a third party app that requires the data to be a double array).  I've tried to convert the data to a string in this way to pass it to my application:
SELECT Cast(Start AS CHAR(19)) FROM MyTable
and then converting that string in my application to a double.  This approach does not work because I cannot convert the string to a double.  What is a good approach to this?
解决方案
By double I assume you mean floating point.

datetime2(0) can be cast to datetime first (without loss of precision), which casts to float easily

Assuming you don't have values before 1753
SELECT CAST(CAST(Start AS datetime) AS float) FROM MyTable
Example
DECLARE @foo datetime2(0) = GETDATE()
SELECT @foo, CAST(CAST(@foo AS datetime) AS float)

-- 2018-09-20 11:31:32   43361.4802314815
GO

DECLARE @foo datetime2(0) = GETDATE()
SELECT @foo, CAST(@foo AS float) --error

--Msg 529, Level 16, State 2, Line 5
--Explicit conversion from data type datetime2 to float is not allowed.


                        
这篇关于如何将SQL datetime2转换为double？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！