We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Sybase Converting date format

Peteri99
Peteri99 asked
on
Medium Priority
3,949 Views
Last Modified: 2012-06-22
How can I change Sybase Date Format
FROM:
'Oct 22 2008 1:06:53:743AM"
to
'2008-10-22 01:06:53.743'
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Try using the below query:

replace (convert(char(23), 'Oct 22 2008 1:06:53:743AM', 23), "T", " ")
Principal Consultant
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
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).

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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>
------------------------------------------------------------------------------------------------------
Joe WoodhousePrincipal Consultant
CERTIFIED EXPERT
Most Valuable Expert 2012

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

Try str_replace.

Author

Commented:
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
Joe WoodhousePrincipal Consultant
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
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.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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

Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.