Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-07-05
3
Medium Priority
?
2,361 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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

If you’re involved with your company’s wide area network (WAN), you’ve probably heard about SD-WANs. They’re the “boy wonder” of networking, ostensibly allowing companies to replace expensive MPLS lines with low-cost Internet access. But, are they …
A while back, I ran into a situation where I was trying to use the calculated columns feature in SharePoint 2013 to do some simple math using values in two lists. Between certain data types not being accessible, and also with trying to make a one to…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

721 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