If I were to read the bcp out file, and I had the 8 bytes of the datetime
field in the file, how would I convert those 8 bytes into a datetime?

Harolds (harolds@nospam.nospam) writes:

The first four bytes are the number of days since 1900-01-01, the second
four bytes are the number of ticks since midnight. A tick in this context
is 3.333 milliseconds.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Hi Harolds,
Since the datetime column was stored with 8 bytes, I think that you were using native storage file type to save the bcp output and you wanted to read the output file (via T-SQL statement?).
If I am off base, please let me know.

To read the data, you can create a matched format file and then use OPENROWSET to read the data. For example, I have a table with the following definition and data.
USE TESTDB
GO
CREATE TABLE [dbo].[TEST](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Title] [varchar](200) NOT NULL,
[TestDate] [datetime] NOT NULL DEFAULT (getdate()),
)
GO
INSERT INTO TEST VALUES('test1','2008-08-31')
INSERT INTO TEST VALUES('test2','2008-07-31')
INSERT INTO TEST VALUES('test3','2008-08-22')
GO

Then I ran the following bcp command to export the data to a file with native data type:
C:\>bcp TestDB..Test out d:\data\testdata.dat -n -T

Hi Harolds,
Just check with you what this issue is going on. If you have any other questions or concerns, please
feel free to let us know.

Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

I used you bcp out on a table in my db, and then tried bcp in as well as your
bulk select and both returned an error.

bcp out:
bcp "select top 1000 * from activity.dbo.activity200807" queryout
f:\testdata.dat -n -T

format:
bcp "activity.dbo.activity200807" format nul -n -f f:\testdata.fmt -T

select:
SELECT a.* FROM OPENROWSET(BULK N'f:\TestData.dat',FORMATFILE
='f:\testdata.fmt') as a

Harolds (harolds@nospam.nospam) writes:

SQL SERVER 2005

The same thing happens when doing an out as with queryout.

Format:
9.0
8
1 SQLUNIQUEID 1 16 "" 1 ActivityID
""
2 SQLDATETIME 0 8 "" 2 DateTime
""
3 ...

Hi Harolds,
I performed a test at my side with the following data:
--------------------------------------------------------------------
DECLARE @n int
SET @n = 0
WHILE @n<1000
BEGIN
INSERT INTO dbo.Activity200809 VALUES(newid(),getdate(),'Page1','SELECT * FROM Page1','@forms1,@forms2,@forms3,..............','@sessions1,@sessions2,@sessions3,..............','@servers1,
@servers2,@servers3,..............','Test')
SET @n=@n+1
END
----------------------------------------------------------------------
Then I used bcp command same as yours to export data and format to files and then run the following query:
SELECT a.* FROM OPENROWSET(BULK N'D:\Data\activity200809.dat',FORMATFILE ='D:\data\activity200809.fmt') as a

However I could not reproduce your issue. For further research, is it convenient for you to export your table to a test database and send the backup file of the test database to me
(changliw_at_microsoft_dot_com)?

Thanks!

Harolds (harolds@nospam.nospam) writes:

This thread was started because the monthly bcp files since March of 2006 are
all corrupt, and I am trying to recover more the 2TB's worth of data for an
unrepairable DB and I needed to get the date and of a datetime field correct.
The DB has a table for each month since November of 2004, and bcp was used
because SQL Server does not have the ability to backup individual tables, and
backing up the entire database (plus incremental backups) was not feasible.

The database is designed the way it should be (if you don't have anything
positive to add, keep you comments to yourself), using a view and table
constraints.

BCP IN also produces an error: "String data, right truncation" and then ends
without anything being copied.

Since bcp has proved to be unreliable, we are going to put each individual
table into its own DB (since using a view with table constraints still
works), that way we can have separate backups for each table as well as do
incremental backups on the current year/month table.
--
Harolds

"Erland Sommarskog" wrote: