Tags: 2gt, convert, database, datatype, datetime, following, gomsg, int, invoice_date, mysql, od_invoice, oracle, please1gt, query, select, sql, sybase

Convert datatype INT to DATETIME

On Database » sybase

3,183 words with 2 Comments; publish: Mon, 05 Nov 2007 21:53:00 GMT; (250142.58, « »)

Can anyone help me on the the following (simple) query please:

1> select invoice_date from od_invoice where invoice_date = "01.01.2003"

2> go

Msg 257, Level 16, State 1:

Line 1:

Implicit conversion from datatype 'VARCHAR' to 'SMALLINT' is not allowed. Use the CONVERT function

to run this query.

1>

1> select * from od_invoice where invoice_date = 01.01.2003

2> go

Msg 102, Level 15, State 1:

Line 1:

Incorrect syntax near '.2003'.

1>

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"

2> go

invoice_date

----

10532

(1 row affected)

1>

I get no dates, but just numbers - but in the application - I can see the actual date

Thanks

Mick

All Comments

Leave a comment...

  • 2 Comments
    • SMALLINT is NOT a datetime column.

      >10532

      The results of your query show no meaningful date or time value there. Your application is doing something bizzare to turn that into a date and time value. Figure out what that is, then you can perform queries on it.

      #1; Thu, 13 Dec 2007 12:48:00 GMT
    • Hi,

      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')

      from od_invoice

      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"

      Regards,

      Mark

      === Original Words ===

      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"

      2> go

      Msg 257, Level 16, State 1:

      Line 1:

      Implicit conversion from datatype 'VARCHAR' to 'SMALLINT' is not allowed. Use the CONVERT function

      to run this query.

      1>

      1> select * from od_invoice where invoice_date = 01.01.2003

      2> go

      Msg 102, Level 15, State 1:

      Line 1:

      Incorrect syntax near '.2003'.

      1>

      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"

      2> go

      invoice_date

      ----

      10532

      (1 row affected)

      1>

      I get no dates, but just numbers - but in the application - I can see the actual date

      Thanks

      Mick

      #2; Thu, 13 Dec 2007 12:49:00 GMT