Go Premium for a chance to win a PS4. Enter to Win

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

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

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
aquasw
Asked:
aquasw
  • 2
1 Solution
 
TadHawkinsCommented:
You want to convert to char(26), not datetime. The formatting (the 121) has no meaning unless you are converting to char type.
0
 
aquaswAuthor Commented:
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
 
TadHawkinsCommented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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