This works perfectly on the server (sql server 2012) for a julian date of 5 digits
select cast (column1 as DATETIME) FROM mytable
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.
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.
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 ?
Assuming date is in YYYYMMDD format. Use below:
SELECT CONVERT(DATETIME, CONVERT(VARCHAR, col1)) AS someCol FROM dbo.mytable
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 */
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 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, " ")
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 */
