MS SQL Server - datetime conversion question

Author

datetime conversion question

WANNABE

2006-11-08, 7:12 pm

Using SQL2005 DTS - I am trying to import data from a CSV file into a table
created with the following
CREATE TABLE [maint].[dbo].& #91;SpaceAnalysis_v2
] (
[Date-Time] datetime,
[Server] text,
[Drive] text,
[Drive Size] numeric(29,0),
[Space Free] numeric(29,0)
)
the first field is date and time and looks like this >>
09/20/2006 06:30:03 PM
But no matter what I try to use for a final field format the result of that
data after it's imported displays the same time for every record >> 12:00:00
AM <<. The date comes through fine, but it just does not seem to recognize
the time. What do I need to do to get the time to be imported correctly ???

Arnie Rowland

2006-11-08, 7:12 pm

It appears that the time is not included as part of the date data.

Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
06:30:03 PM') This is a non-standard date format, having two spaces between
the date and time portions, as well as a space between the time and the
AM/PM indicator.

Please post an EXACT excerpt from the import file so that we can visually
see the data to determine if there are problems that are causing a
'mis-load'.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:eXDUZT1AHHA.4472@TK2MSFTNGP03.phx.gbl...
> Using SQL2005 DTS - I am trying to import data from a CSV file into a
> table created with the following
> CREATE TABLE [maint].[dbo].& #91;SpaceAnalysis_v2
] (
> [Date-Time] datetime,
> [Server] text,
> [Drive] text,
> [Drive Size] numeric(29,0),
> [Space Free] numeric(29,0)
> )
> the first field is date and time and looks like this >>
> 09/20/2006 06:30:03 PM
> But no matter what I try to use for a final field format the result of
> that data after it's imported displays the same time for every record >>
> 12:00:00 AM <<. The date comes through fine, but it just does not seem
> to recognize the time. What do I need to do to get the time to be
> imported correctly ???
>

WANNABE

2006-11-09, 7:13 pm

As I paste this in here I just realized that my first post was not
absolutely correct, sorry I was looking at the file through excel.

Thanks for your time Arnie, here are the first 2 lines as
displayed using notepad>>
9/19/2006 16:50,EXCEDE,C,36265
226240,14397304832
9/19/2006 16:50,EXCEDE,D,14700
0000000,41808166912
====================
==================
"Arnie Rowland" <arnie@1568.com> wrote in message
news:O8sLGE3AHHA.3560@TK2MSFTNGP04.phx.gbl...
> It appears that the time is not included as part of the date data.
>
> Is the date/time field enclosed in single quotes? (Such as: '09/20/2006
> 06:30:03 PM') This is a non-standard date format, having two spaces
> between the date and time portions, as well as a space between the time
> and the AM/PM indicator.
>
> Please post an EXACT excerpt from the import file so that we can visually
> see the data to determine if there are problems that are causing a
> 'mis-load'.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
>
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:eXDUZT1AHHA.4472@TK2MSFTNGP03.phx.gbl...
>
>

Sponsored Links