0

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 ?
CM2K
  • 833
  • 3
  • 18
  • 38

2 Answers2

2

Assuming date is in YYYYMMDD format. Use below:

SELECT CONVERT(DATETIME, CONVERT(VARCHAR, col1)) AS someCol FROM dbo.mytable
Meet
  • 408
  • 2
  • 10
  • Thanks, does that also work with int instead of varchar. Will have access to test it in couple of days – CM2K Oct 08 '15 at 05:21
  • I assumed `col1` is `INT` type, so it should be fine. – Meet Oct 08 '15 at 06:27
  • sorry for the delay. I had today a chance to test it. I received: `syntax error durring explicit conversion of VARCHAR value '35766' to a DATETIME field.` – CM2K Oct 12 '15 at 07:58
  • Being new to sybase, i don't quite get this: if i click on the dbo and go to data, I see a preview of my table. In there col1, when i mouse hover it, it reads `data type:INTEGER`. BUT, in the folders menu if i expand the dbo and click on columns `mycol1` is of type SF_Date . That being said, how can I get what I want. This should be an easy thing to do, but for some reason can't get it to work. Thanks – CM2K Oct 12 '15 at 08:07
  • My answer assumed your datetime is in YYYYMMDD format. But `35766` is not datetime. – Meet Oct 12 '15 at 08:48
  • Do `sp_columns mytable` to find out columns datatype – Meet Oct 12 '15 at 08:54
  • data_type 4, type_name int, precision 10, length 4, scale 0, radix 10, nullable 0, remarks null, ss_data_type 56, colid 2, culum_def null,, sql_data_type 4, char_octet_length 0, ordinal_position 2, is_nullable no – CM2K Oct 12 '15 at 09:11
  • so, you need to convert `35766` into datetime? – Meet Oct 12 '15 at 13:32
  • Yes. That's only the first value out of thousands . it threw an error at the first – CM2K Oct 12 '15 at 13:33
  • I'm not sure, looks like it. What i know is, if I take any of those values and place them in excel and switch the data type to short date, it is being displayed correctly. I can give you some examples. `42197` IS 2015-07-14 , `42198` IS 2015-07-15, `42199` is 2015-07-16 – CM2K Oct 13 '15 at 07:28
0

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 */
Corral
  • 86
  • 6