在sybase中将int转换为datetime [英] convert int to datetime in sybase
问题描述
这在服务器(sql server 2012)上的5位数的朱利安日期完美工作
This works perfectly on the server (sql server 2012) for a julian date of 5 digits
select cast (column1 as DATETIME) FROM mytable
如何在sybase中将int转换为datetime?
How to cast an int to datetime in sybase?
这将是最好的方式,因为我有一个大表,我必须最小化我将使用查询下的服务器的时间。
And which would be the best way, since I have a large table and I have to minimize the time i'm going to be using the server under the query.
我在这里看到: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1570/html/sqlug/sqlug645.htm
允许从 int
转换为 varchar
和从 varchar
到
smalldate
。
I saw here: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1570/html/sqlug/sqlug645.htm
that it is allowed to convert from int
to varchar
and from varchar
to smalldate
.
所以可能这样,但我不知道语法对于sybase:
declare @convDate varchar(200)
So maybe something like this, but i don't know the syntax for sybase: declare @convDate varchar (200)
set @convDate = 'SELECT top 100 CONVERT( varchar (200), column1, 104 )as someCol FROM dbo.mytable'
select cast (@convDate as DateTime) as newDate into #myTemp from ?
推荐答案
表示Excel中日期的内部数字是连续的整数从1900年1月1日,这是第一。因此,一个解决方案是使用函数DATEADD将整数(减1)和1900年1月1日相加。在此查询中,与Jan 1 1900相同,因为这是Sybase ASE默认值。
Internal numbers representing dates in Excel are a continuous sequence of integers from Jan 1 1900, which is number one. Hence, a solution is to use the function DATEADD to sum your integer (minus one) to Jan 1 1900. In this query, " " is the same as "Jan 1 1900" as this is the Sybase ASE default.
select dateadd(day, column1 - 1, " ") from mytable /* Probably wrong */
但我测试了一天的差异。其结果是2015年7月13日,但Excel表示2015年7月12日。
But I tested and got a one day difference. The result of this is Jul 13 2015, but Excel shows Jul 12 2015 instead.
select dateadd(day, 42197 - 1, " ")
IMHO,Excel是错误的,因为它显示1960年2月29日的数字60,但1900(与2000年相反)不是闰年。 Sybase ASE是正确的;这给出了1900年2月28日和1900年3月1日:
IMHO, Excel is wrong, as it shows Feb 29 1900 for the number 60, but 1900 (contrary to 2000) is not a leap year. Sybase ASE is correct; this gives Feb 28 1900 and Mar 1 1900:
select dateadd(day, 59 - 1, " "), dateadd(day, 60 - 1, " ")
假设你必须采用Excel约定,一个:
Assuming you had to take Excel convention, then just subtract two instead of one:
select dateadd(day, column1 - 2, " ") from mytable /* Bizarre but maybe OK */
这篇关于在sybase中将int转换为datetime的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!