Re: Convert datatype INT to DATETIME
This is how you convert an int field into a datetime. I assume you are storing the data as an int becuase you want to store dates to the exact millisecond and sybase only stores datetime fields to the nearest 1/3 second. If its a java app you are using, there is a java function that convert milliseconds into a datetime field without all the hassle of doing it on the db like below. Anyway, this is what you do:
select dateadd(second,convert(int,(invoice_date /1000)), 'Jan 1 1970')
where dateadd(second,convert(int,(invoice_date /1000)), 'Jan 1 1970') like "Jan 1 2003%"
I added the like statement because I doubt you will get rows back to exactly 'Jan 1 2003 12:00AM' which is what invoice_date = "01.01.2003"
Originally posted by mickge
Can anyone help me on the the following (simple) query please:
1> select invoice_date from od_invoice where invoice_date = "01.01.2003"
Msg 257, Level 16, State 1:
Implicit conversion from datatype 'VARCHAR' to 'SMALLINT' is not allowed. Use the CONVERT function
to run this query.
1> select * from od_invoice where invoice_date = 01.01.2003
Msg 102, Level 15, State 1:
Incorrect syntax near '.2003'.
As you are unable to convert SMALLINT to DATETIME - What are my options here ?
The date/invoice_date column is smallint
If I do a:
1> select invoice_date from od_invoice where invoiceno = "00420"
(1 row affected)
I get no dates, but just numbers - but in the application - I can see the actual date
Senior Sybase DBA