My flat file I import to the table is set up as 2004/06/16 09:40:07.994 comma delimited, and i import this using DTS into a datetime field. but when I run a query on the table, the only thing I see is 2004/06/16 showing up, how come I can't see the time?
Thx for your help!What tool are you using to query the table? It may be set to display DATETIME values as only the date (this is common if using the regional settings on the client with an ODBC based tool).
-PatP
Showing posts with label datetime. Show all posts
Showing posts with label datetime. Show all posts
Monday, March 12, 2012
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...
>
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...
>
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...
>
Wednesday, March 7, 2012
Importing data from Excel to SQL
I populate SQL tables with excel data and need to have one of the fields be <NULL>.
The field is a DateTime field and when imported places a date of 1900-01-01 00:00:00.000 I need that field to be NULL. Is there a way to represent NULL in excel so when it is importing it is not transformed to a date?
ThanksThe problem is not how your data is represented in Excel, but how it is being imported, or possibly the defaults on your SQL table. How are you importing the Excel data?
blindman
The field is a DateTime field and when imported places a date of 1900-01-01 00:00:00.000 I need that field to be NULL. Is there a way to represent NULL in excel so when it is importing it is not transformed to a date?
ThanksThe problem is not how your data is represented in Excel, but how it is being imported, or possibly the defaults on your SQL table. How are you importing the Excel data?
blindman
Subscribe to:
Posts (Atom)