Solved

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

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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

This article is in regards to the Cisco QSFP-4SFP10G-CU1M cables, which are designed to uplink/downlink 40GB ports to 10GB SFP ports. I recently experienced this and found very little configuration documentation on how these are supposed to be confi…
Compliance and data security require steps be taken to prevent unauthorized users from copying data.  Here's one method to prevent data theft via USB drives (and writable optical media).
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

617 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