Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012

    Unanswered: Convert date time format Sybase db

    Hi Guys/Experts,

    I need your help to convert the current date time format yyyy-mm-ddThh:mm:ss into format.

    For example

    Select * from info where date != 'Null'

    Name Date

    John 2009-11-05T00:00:00
    Greg 2010-08-01T00:00:00

    Now, i want to select the date in format. Date column is a varchar.
    I tried the below statement but it returns an error "syntax error during explicit conversion of varchar value to a datetime field"

    Select date from info
    Where convert(datetime, "date", 102) =! 'Null'

    Any help would be greatlt appreciated.

  2. #2
    Join Date
    Jan 2004
    Provided Answers: 4
    First, select records where the date-column is not empty:
    select date from info where date is not null

    Next, substitute the T in the date with a space, or the conversion to datetime will fail:
    select str_replace(date, 'T', ' ') from info where date is not null

    The string should now read something like 2009-11-05 00:00:00

    If you need the value in a datetime-datatype, convert the string to a datetime
    select convert(datetime(str_replace(date, 'T', ' ')) from info where date is not null
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    May 2012
    Hi Martijnvs,

    Thanks for the reply.

    Basically, I need the "convert" command to be placed in the "where" clause coz I'll be using it on joining tables and "date" is not a required column so its not in the select command. I will use the date to compare with another date.

    Declare @str varchar(20)
    Set @str='2011.05.05'

    Select name, address
    from table1 to
    left join table2 ta
    on =
    where convert(datetime, "date", 102) <= @str

    *i dont need the date to be a datetime datatype as long as I can do the comparison to another date.

  4. #4
    Join Date
    May 2012
    This is ok now.

    I just used ...
    Where left(date, 10) <= str_replace(@str,'.','-')


Tags for this Thread

Posting Permissions

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