Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

BCP out smalldatetime

2 posts in General Discussion Last posting was on 2010-07-14 20:56:49.0Z
Pauline Schneider Posted on 2010-07-14 20:18:26.0Z
Sender: b77.4c3e1614.1804289383@sybase.com
From: Pauline Schneider
Newsgroups: sybase.public.ase.general
Subject: BCP out smalldatetime
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4c3e1b92.da3.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 14 Jul 2010 13:18:26 -0700
X-Trace: forums-1-dub 1279138706 10.22.241.41 (14 Jul 2010 13:18:26 -0700)
X-Original-Trace: 14 Jul 2010 13:18:26 -0700, 10.22.241.41
Lines: 16
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29365
Article PK: 78597

I am trying to bcp out a table with smalldatetime field .
When I looked at the data the field is missing the seconds .

Command I am using :

bcp "[static].[dbo].[memo_burst_dates]" out
"static.dbo.memo_burst_dates.dat" -c -t "<EOFD>" -r
"<EORD>" -Udba_local -Pl0cali
d -SSMARTNA_DEV

I think I need to use a format file to get the complete
datetime field .

Is there a stored proc that will create the format file ?

Thanks


"Mark A. Parsons" <iron_horse Posted on 2010-07-14 20:56:49.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: BCP out smalldatetime
References: <4c3e1b92.da3.1681692777@sybase.com>
In-Reply-To: <4c3e1b92.da3.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c3e2491@forums-1-dub>
Date: 14 Jul 2010 13:56:49 -0700
X-Trace: forums-1-dub 1279141009 10.22.241.152 (14 Jul 2010 13:56:49 -0700)
X-Original-Trace: 14 Jul 2010 13:56:49 -0700, vip152.sybase.com
Lines: 56
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29366
Article PK: 78598

The smalldatetime datatype is accurate to the minute so the seconds are always going to be ':00' anyway, ie, no need to
export the seconds.

The datetime datatype is accurate to ~3 ms, so not only do you get seconds but also 3 decimals of accuracy (ie, ms) when
bcp'ing out a datetime value.

============================
use tempdb
go
create table t1(a int, b smalldatetime, datetime)
go
insert t1 select 1,getdate(),getdate()
go
select * from t1
go
a b c
----------- -------------------------- --------------------------
1 Jul 14 2010 4:48PM Jul 14 2010 4:48PM

============================

$ bcp tempdb..t1 out t1.bcp -Usa -P... -SCC1 -c

Starting copy...

1 rows copied.
Clock Time (ms.): total = 15 Avg = 15 (66.67 rows per sec.)

$ cat t1.bcp
1 Jul 14 2010 4:48PM Jul 14 2010 4:48:13:606PM
^^ ^^^
accurate to min accurate to ~3ms
============================


Is there a reason you want/need to see the ':00' for the smalldatetime values?

Pauline Schneider wrote:
> I am trying to bcp out a table with smalldatetime field .
> When I looked at the data the field is missing the seconds .
>
> Command I am using :
>
> bcp "[static].[dbo].[memo_burst_dates]" out
> "static.dbo.memo_burst_dates.dat" -c -t "<EOFD>" -r
> "<EORD>" -Udba_local -Pl0cali
> d -SSMARTNA_DEV
>
> I think I need to use a format file to get the complete
> datetime field .
>
> Is there a stored proc that will create the format file ?
>
> Thanks