Sybase Converting date format

Posted on 2009-02-09
Last Modified: 2012-06-22
How can I change Sybase Date Format
'Oct 22 2008 1:06:53:743AM"
'2008-10-22 01:06:53.743'
Question by:Peteri99
    LVL 57

    Expert Comment

    by:Raja Jegan R
    Try using the below query:

    replace (convert(char(23), 'Oct 22 2008 1:06:53:743AM', 23), "T", " ")
    LVL 24

    Accepted Solution

    I assume you mean change how it is displayed in an SQL statement, since it's not actually stored in either format within the database itself.

    You don't tell us which Sybase product (they have more than one database) or version. I'm going to assume it's ASE of either 12.5.x or 15.0.x vintage... but this answer would work for most earlier versions of ASE as well.

    Unfortunately there is no predefined datetime format which is exactly what you want, we'll have to do it the hard way:

    convert(char(4),datepart(yy,<datetime>))+"-"+convert(char(2),datepart(mm,<datetime>))+"-"+convert(char(2),datepart(dd,<datetime>))+" "+convert(char(8),<datetime>,108)+"."convert(char(3),datepart(ms,<datetime>))

    A bit of a mouthful, eh?

    If you're using ASE 15.0.x (or 12.5.x and have paid for the Java option) you could write your own function (in SQL in 15.0.x, or in Java in both versions, but you pay extra for this in 12.5.x).

    Author Comment

    Thanks for your prompt repky
    I tried your query but it is giving me an  error
    Please see below
    1> replace(convert(char(23), 'Oct 22 2008 1:06:53:743AM', 23), "T", " ")
    Msg 156, Level 15, State 2:
    Server 'CONTROLDB2', Line 1:
    Incorrect syntax near the keyword 'replace'.
    LVL 24

    Expert Comment

    by:Joe Woodhouse
    Sybase doesn't have a "replace" function. :)

    Try str_replace.

    Author Comment

    Hi joe,
    I am using ASE 15.01
    I am trying to bcp data from ASE sybase 15.01 and then load this file into MS SQL server 2005
    I am having problems trying to load this bcp file due to difference in the date format.
    Sybase out put date in
    "Nov  1 2008  1:06:53:743AM|312996|Nov  8 2008  9:21:00:000PM|Scheduler|0|0|2|Taxybike|0|INVOICE"
    MS SQL Server 2005 requires
    2008-10-22 00:00:07.860|166365|2008-10-22 08:20:00.000|Jasmine Telesford|0|0|2|Standard Car (1-4 Passengers)|0|INVOICE

    when I changed the bcp file (only 2 records), I could load the data successfully
    What I am trying to do  create is
    create a tmp table.
    insert data into this tmp table. with date format of "2008-10-22 08:20:00.000" into a char field
    then bcp this data out
    load is data into MS SQL Server
    LVL 24

    Expert Comment

    by:Joe Woodhouse
    I'm surprised MS SQL 2005 can't handle that, but this is easily fixed.

    Create a view in Sybase, use the expression I gave you for the datetime column (and name it after the datetime column). You can now bcp out of the view. It will all be formatted in the way you want. You don't even need the temp table.
    LVL 57

    Expert Comment

    by:Raja Jegan R
    SQL Server will definitely handle it.

    Default Datetime formats of SQL Server are

    mon dd yyyy hh:miAM and mon dd yyyy hh:mi:ss:mmmAM .

    Convert it into this format either 100 / 109 in Sybase and you would be able to successfully bcp the datetime values into SQL Server.

    Author Comment

    Thanks Raja & Joe

    I have combined both your methods as below, and got the required answer.
    1> select str_replace(convert(varchar(23), getdate(), 23), 'T', ' ')+'.'+convert(char(3),datepart(ms,getdate()))

     2009-02-09 13:58:20.526

    (1 row affected)

    Once again Thank you v. much.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
    In the modern office, employees tend to move around the workplace a lot more freely. Conferences, collaborative groups, flexible seating and working from home require a new level of mobility. Technology has not only changed the behavior and the expe…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now