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...
>