[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3258
  • Last Modified:

Sybase Converting date format

How can I change Sybase Date Format
FROM:
'Oct 22 2008 1:06:53:743AM"
to
'2008-10-22 01:06:53.743'
0
Peteri99
Asked:
Peteri99
  • 3
  • 3
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Try using the below query:

replace (convert(char(23), 'Oct 22 2008 1:06:53:743AM', 23), "T", " ")
0
 
Joe WoodhousePrincipal ConsultantCommented:
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).
0
 
Peteri99Author 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>
------------------------------------------------------------------------------------------------------
0
Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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

Try str_replace.
0
 
Peteri99Author 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
0
 
Joe WoodhousePrincipal ConsultantCommented:
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.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.
0
 
Peteri99Author 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.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now