Sybase Converting date format

Try using the below query:

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

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", " ")
2>
Msg 156, Level 15, State 2:
Server 'CONTROLDB2', Line 1:
Incorrect syntax near the keyword 'replace'.
1>
------------------------------------------------------------------------------------------------------

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

Try str_replace.

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"
format
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

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.

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.

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()))
2>

 ------------------------------------------------------
 2009-02-09 13:58:20.526

(1 row affected)
-----------------------------------------------------------------------------------------------

Once again Thank you v. much.