Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    12

    Unanswered: Convert datatype INT to DATETIME

    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. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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.
    Thanks,

    Matt

  3. #3
    Join Date
    Jun 2002
    Location
    Dublin, Ireland
    Posts
    23

    Re: Convert datatype INT to DATETIME

    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



    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"
    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
    Mark Sheehan,
    Senior Sybase DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •