Solved

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

Posted on 2006-07-05
3
2,338 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
  • 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Short answer to this question: there is no effective WiFi manager in iOS devices as seen in Windows WiFi or Macbook OSx WiFi management, but this article will try and provide some amicable solutions to better suite your needs.
A safe way to clean winsxs folder from your windows server 2008 R2 editions
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the fileā€¦
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.

760 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

24 Experts available now in Live!

Get 1:1 Help Now