Importing date w/SQLExpress appends '.000' to field

Hello
I have a DOB field in text file I am importing into an SQLExpress database
with the field data type is set to 'datetime'. For some reason the date
imports as 1958-08-01 00:00:00.000 ( with a decimal and 3 zeros appended to
the end of the field ). What am I doing wrong? There is no time associated
with this date in the flat file.
Thanks for your help...Dale,
That is the normal action for a datetime. The datetime datatype includes
hours, minutes, seconds, and milliseconds. If you store a date (without the
time value), the default time is midnight, or 00:00:00.000.
If possible, you may wish to change the datatype for the DOB field to
smalldatetime. Smalldatetime will have only hours and minutes for the time
portion -but they too will be set to the default, midnight, e.g., 00:00.
When you retrieve data, you can always remove the time portion either in the
data retrieval query, or in the display application or reporting
application. For T-SQL, look in Books Online for "CAST and CONVERT" for
information about datetime formats.
Likewise, if you were to have a field that you wanted only time values, and
if you inserted [12:45 PM] into that field, SQL Server will add the default
date value, 01/01/1900.
I hope this helps understand what is going on, and points you in a direction
to more relevant information.
Regards,
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dale" <dale@.nospam.com> wrote in message
news:OOMGHdxwGHA.3964@.TK2MSFTNGP04.phx.gbl...
> Hello
> I have a DOB field in text file I am importing into an SQLExpress database
> with the field data type is set to 'datetime'. For some reason the date
> imports as 1958-08-01 00:00:00.000 ( with a decimal and 3 zeros appended
> to the end of the field ). What am I doing wrong? There is no time
> associated with this date in the flat file.
> Thanks for your help...
>|||Thanks Arnie
I guess what threw me is the behaviour isn't consistent sometimes the import
has the '.000' appended and other times not despite having the data type as
datetime and the flat file is always the same.
But at least you indicated it wasn't something I was doing.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23O$VCnywGHA.1484@.TK2MSFTNGP04.phx.gbl...
> Dale,
> That is the normal action for a datetime. The datetime datatype includes
> hours, minutes, seconds, and milliseconds. If you store a date (without
> the time value), the default time is midnight, or 00:00:00.000.
> If possible, you may wish to change the datatype for the DOB field to
> smalldatetime. Smalldatetime will have only hours and minutes for the time
> portion -but they too will be set to the default, midnight, e.g., 00:00.
> When you retrieve data, you can always remove the time portion either in
> the data retrieval query, or in the display application or reporting
> application. For T-SQL, look in Books Online for "CAST and CONVERT" for
> information about datetime formats.
> Likewise, if you were to have a field that you wanted only time values,
> and if you inserted [12:45 PM] into that field, SQL Server will add the
> default date value, 01/01/1900.
> I hope this helps understand what is going on, and points you in a
> direction to more relevant information.
> Regards,
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Dale" <dale@.nospam.com> wrote in message
> news:OOMGHdxwGHA.3964@.TK2MSFTNGP04.phx.gbl...
>> Hello
>> I have a DOB field in text file I am importing into an SQLExpress
>> database with the field data type is set to 'datetime'. For some reason
>> the date imports as 1958-08-01 00:00:00.000 ( with a decimal and 3 zeros
>> appended to the end of the field ). What am I doing wrong? There is no
>> time associated with this date in the flat file.
>> Thanks for your help...
>

Importing date w/SQLExpress appends '.000' to field

Hello
I have a DOB field in text file I am importing into an SQLExpress database
with the field data type is set to 'datetime'. For some reason the date
imports as 1958-08-01 00:00:00.000 ( with a decimal and 3 zeros appended to
the end of the field ). What am I doing wrong? There is no time associated
with this date in the flat file.
Thanks for your help...Dale,
That is the normal action for a datetime. The datetime datatype includes
hours, minutes, seconds, and milliseconds. If you store a date (without the
time value), the default time is midnight, or 00:00:00.000.
If possible, you may wish to change the datatype for the DOB field to
smalldatetime. Smalldatetime will have only hours and minutes for the time
portion -but they too will be set to the default, midnight, e.g., 00:00.
When you retrieve data, you can always remove the time portion either in the
data retrieval query, or in the display application or reporting
application. For T-SQL, look in Books Online for "CAST and CONVERT" for
information about datetime formats.
Likewise, if you were to have a field that you wanted only time values, and
if you inserted [12:45 PM] into that field, SQL Server will add the defa
ult
date value, 01/01/1900.
I hope this helps understand what is going on, and points you in a direction
to more relevant information.
Regards,
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dale" <dale@.nospam.com> wrote in message
news:OOMGHdxwGHA.3964@.TK2MSFTNGP04.phx.gbl...
> Hello
> I have a DOB field in text file I am importing into an SQLExpress database
> with the field data type is set to 'datetime'. For some reason the date
> imports as 1958-08-01 00:00:00.000 ( with a decimal and 3 zeros appended
> to the end of the field ). What am I doing wrong? There is no time
> associated with this date in the flat file.
> Thanks for your help...
>|||Thanks Arnie
I guess what threw me is the behaviour isn't consistent sometimes the import
has the '.000' appended and other times not despite having the data type as
datetime and the flat file is always the same.
But at least you indicated it wasn't something I was doing.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23O$VCnywGHA.1484@.TK2MSFTNGP04.phx.gbl...
> Dale,
> That is the normal action for a datetime. The datetime datatype includes
> hours, minutes, seconds, and milliseconds. If you store a date (without
> the time value), the default time is midnight, or 00:00:00.000.
> If possible, you may wish to change the datatype for the DOB field to
> smalldatetime. Smalldatetime will have only hours and minutes for the time
> portion -but they too will be set to the default, midnight, e.g., 00:00.
> When you retrieve data, you can always remove the time portion either in
> the data retrieval query, or in the display application or reporting
> application. For T-SQL, look in Books Online for "CAST and CONVERT" for
> information about datetime formats.
> Likewise, if you were to have a field that you wanted only time values,
> and if you inserted [12:45 PM] into that field, SQL Server will add th
e
> default date value, 01/01/1900.
> I hope this helps understand what is going on, and points you in a
> direction to more relevant information.
> Regards,
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Dale" <dale@.nospam.com> wrote in message
> news:OOMGHdxwGHA.3964@.TK2MSFTNGP04.phx.gbl...
>

Importing csv File into SqlExpress .MDF file.

Hi,
I'm trying to use BCP to import a .csv file into a .mdf file.
At the commandline, I'm using the following command:
bcp commercedb.mdf.crmc_products in test.csv -T
I'm getting errors (below) telling me that I'm not successfully
connecting to the database.
Any help would be appreciated. If you can help me, please understand
I'm new to this so limited g speak would be most appreciated.
ERRORS:
SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not
open a con
nection to SQL Server [53].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Native Client]An error has occurred while
establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may
be caused by the fact that under the default settings SQL Server does
not allow
remote connections.Data access libraries do not directly access (MDF) SQL Server database files
the same way they can an (MDB) MS Access file. All data access must go
through the SQL Server service itself.
The .mdf database file must first be attached to SQL Server.
http://msdn2.microsoft.com/en-us/library/ms165673.aspx
Once done, you bulk copy specifying the server and database name:
http://msdn2.microsoft.com/en-us/library/ms162802.aspx
<tom.herz@.gmail.com> wrote in message
news:1138982708.766097.92140@.f14g2000cwb.googlegroups.com...
> Hi,
> I'm trying to use BCP to import a .csv file into a .mdf file.
> At the commandline, I'm using the following command:
> bcp commercedb.mdf.crmc_products in test.csv -T
> I'm getting errors (below) telling me that I'm not successfully
> connecting to the database.
> Any help would be appreciated. If you can help me, please understand
> I'm new to this so limited g speak would be most appreciated.
> ERRORS:
> SQLState = 08001, NativeError = 53
> Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not
> open a con
> nection to SQL Server [53].
> SQLState = HYT00, NativeError = 0
> Error = [Microsoft][SQL Native Client]Login timeout expired
> SQLState = 08001, NativeError = 53
> Error = [Microsoft][SQL Native Client]An error has occurred while
> establishing a
> connection to the server. When connecting to SQL Server 2005, this
> failure may
> be caused by the fact that under the default settings SQL Server does
> not allow
> remote connections.
>|||Hi,
Thanks. This is what I needed. I hadn't attached the database.
I haven't been successful yet, but that is a lack of experience with
all the switches and BCP overall.
Once I got the database attached, things started to gel.
I really appreciate your time, JT.
Have a good day.

Importing Access database into SQLExpress

I am trying to complete a lab in the Microsoft self-paced training kit, Developing Web Applications with Microsoft Visual C# .NET.

They have provided an Access database named Contacts.mdb and a batch file named InstContacts.bat that uses InstContacts.sql to import the data.

I have installed SQLExpress, but when I run the batch file I get the following:

C:\Microsoft Press\...\data>rem The following command line installs the Contact SQL database

C:\Microsoft Press\...\data>osql -i InstContacts.Sql -E
[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Nevermind, I found the answer here.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=421593&SiteID=1