Solved

Export view from sybase with diffrent date format using bcp command.

Posted on 2006-07-05
3
2,348 Views
Last Modified: 2008-01-09
Hi.
I want to export a view from Sybase database using bcp command.
I have a timestamp field that I want to get in different format in the file.
In the view I am using the convert command to the needed format.
When I export the file it is not in the right format.
I am trying to change the format in the view but always get the same format in the bcp file.
The needed timestamp format is: 2003-02-27 17:22:12.122
The timestamp format I getting in the file is: Jul  5 2006  5:14:00:000PM

I am creating the view by using:

CREATE VIEW view7 AS select convert (datetime, mydatabase..myTable.DATETIME1, 121)As DATETIME1_NEW from
mydatabase..myTable

My bcp command is:
bcp mydatabase..view7 out view7.txt -S SERVER -U username -P password -c > C:/bcp/view7Error.txt

Thanks in advance.
0
Comment
Question by:aquasw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 2

Expert Comment

by:TadHawkins
ID: 17043564
You want to convert to char(26), not datetime. The formatting (the 121) has no meaning unless you are converting to char type.
0
 

Author Comment

by:aquasw
ID: 17043684
When I am using char to create the view:

CREATE VIEW view7 AS select convert (char(26), mydatabase..myTable.DATETIME1, 121)As DATETIME1_NEW from
mydatabase..myTable

I am getting this error:

121 is not a valid style number when converting from DATETIME to a character string.

CTLIB Message:  - L0/O0/S0/N0/0/0:
blk_init(): blk layer: CT library error: Failed when CT_Lib routine ct_results() called.
blk_init failed.
0
 
LVL 2

Accepted Solution

by:
TadHawkins earned 500 total points
ID: 17043976
This might work...

 convert (char(10), d, 111)                    
+space(1)                                      
+convert (char( 8), d, 108)                    
+right (str (datepart (ms, d) * 0.001, 5, 3), 4)

if you don't mind / instead of - as the date part seperator. Otherwise I think you have to do more build-it-from-scratch formatting.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Taking long time 7 489
How to install new sybase instance in Linux 4 733
Installing Sybase IQ 16.x x64 on Windows 8.1 update1 5 1,613
SQL Update query 9 369
We asked our MSP customer base what their favorite tools were and how they help them serve clients. We focused our questions on favorite tools in the following categories: >PSA tools >RMM tools >Alert management tools >Communication tools and Mo…
An introduction to the wonderful sport of Scam Baiting.  Learn how to help fight scammers by beating them at their own game. This great pass time helps the world, while providing an endless source of entertainment. Enjoy!
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

738 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