Importing Null Date Fields

I'm using SQL Server Express and am trying to import a CVS file. The CVS file contains a string field (named DAS) that represents a Date. This field can be null.

I've tried using the DTS Wizard to import this CVS file and convert the DAS field to a Date, which works great until it hits a record with a NULL DAS field. It then throws a convertion error.

Still using the DTS Wizard, I've changed the DataType of the DAS field in the Source file to [DT_DATE], it works fine but all the null dates are converted to 12/30/1899.

Is there a way (DTS Wizard or something else) that will allow me to import these CVS files with null Date fields and keep them as null in SQL Server table.

Thanks for any help,

Jon

Hi,

I read your post and I can advice you the next:

1. Once you have date 12/30/1899 instead all NULL values in SQL Server => you may easy to sort that records, to mark them and place NULL value with “copy” and “paste”. It is easy and quickly.

2. You may write a program instead DTS Wizard /I don’t know that wizard/. That program will read from your CVS file and write to SQL server. From the program you will have full control on all fields. I personally prefer to write a program when I have some unusual case.

I hope that advices will solve the problem. But if you still can’t make NULL values – let me know.

Regards,

Hristo Markov

|||Off the top of my head, I'm thinking there is an option to "Keep Nulls" inside the wizard. I haven't run it and I'm not on my machine w/ SSIS installed so I can't verify it. If you do see such an option, that is how you tell SSIS to preserve the NULLs.

If this isn't an option, then you'll have to go one step deeper and either do custom transformations or write an EXECUTE SQL TASK that will go through and update any record of 12/30/1899 to be NULL.

Importing from access

I have an access table with multiple data fields. When I add the fields into my crystal report all of the data fields show data except the date field.

I then imported the same table into a ms querry and the date field pulled the data in excel.

So there is data when I pull it into excel, but none when I pull it into crystal.

Any suggestions?Check the settings for --> File / Options / Fields / DateTimesql

Importing DATE with Timestamp(In a Flat file) Column using SSIS

Hi

SSIS is brand new for me.. Playing with since a few hours..

Iam trying to import a Flat File into the SQLSERV DB using SSIS..
One of the column is in this format -- "YYYYMMDDHH24MISS"

How do i get around this to import the data in a readable fashion into the Destination?

Thanks!
MKR

Hi MKR,

What data type are you wanting the result to be?

You can use a derived column component to parse the format of the column and create anything you like -- a DT_DBTIMESTAMP, a string with your own format, etc...

You could turn the string in the format you have above into a string with this format: "YYYY-MM-DD HH:MM: SS" with an expression like this in derived column (where i am assuming the string is in a column called 'Col'):

SUBSTRING(Col, 1, 4) + "-" + SUBSTRING(Col, 5,2) + "-" + SUBSTRING(Col, 7,2) + " " + SUBSTRING(Col, 9,2) + ":" + SUBSTRING(Col, 13,2) + ":" + SUBSTRING(Col, 15,2)

Is that the sort of thing you are looking for?

Thanks
Mark

|||Thanks Mark..

But as i was telling you earlier.. My Knowledge on SSIS is very limited..
Now that i know we can manipulate the string..

Where do i do this -- I mean, where do i add this SUBSTRING Manipulation..

|||You want to add in in the data flow, using a derived column transformation.|||Thanks! Welch n Mark

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 Date Values from a flat file into the database

Hi

I am trying to to import a flat file into a table in my database, i get all the values right except for the date, it keeps on inserting NULL values into the date fields.

The date format in the flat file is '20070708' etc.

Does anyone know what i can do to fix this?

I've tried to change the datatype values that it imports, but it still ignores it and inserts NULL values

Any help will be greatly appreciated

Kind Regards

Carel Greaves

Search this forum for "YYYYMMDD" and you'll find your answer along with examples.

Basically, you need to substring the date field into the various date parts and then assemble a date in the format of perhaps mm/dd/yyyy before converting into a datetime field.|||

Thanks

Importing Data through DTS

Hi all,
I have a problem when I import a text delimited data into SQL Server. This happens only with the date. My date format is dd/mm/yyyy in both the text file and regional settings in Windows. I have created a DTS package and schedule the job to run daily. The
imported data in SQL Server show mm/dd/yyyy.
The funny part is when I execute the package directly from DTS, the data were imported without the problem. But when I execute the job in SQL Server Agent, the problem arises. Also this happens only early of the month, from 1st - 12th.
I would appreciate it if anyone can help me in resolving this problem.
ps : My data type for the date column is datetime.
when you execute the job in SQL Server Agent, it runs with the settings on the server.
when you execute the package directly from DTS, it runs with your local settings.
It suggests that the regional settings in Windows on the server are mm/dd/yyyy - which explains why it works until the 12th.
|||Hi Rubes,
I have checked on the server's regional settings, the date format is
'dd/MM/yyyy'.
However, the problem still occurs.
Uskaka
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Adding to my query, I have done the data transformation test and the result shows in format 'dd/MM/yyyy' but when inserted, the format goes 'MM/dd/yyyy'.

Importing Data through DTS

Hi all,
I have a problem when I import a text delimited data into SQL Server. This h
appens only with the date. My date format is dd/mm/yyyy in both the text fil
e and regional settings in Windows. I have created a DTS package and schedul
e the job to run daily. The
imported data in SQL Server show mm/dd/yyyy.
The funny part is when I execute the package directly from DTS, the data wer
e imported without the problem. But when I execute the job in SQL Server Age
nt, the problem arises. Also this happens only early of the month, from 1st
- 12th.
I would appreciate it if anyone can help me in resolving this problem.
ps : My data type for the date column is datetime.when you execute the job in SQL Server Agent, it runs with the settings on t
he server.
when you execute the package directly from DTS, it runs with your local sett
ings.
It suggests that the regional settings in Windows on the server are mm/dd/yy
yy - which explains why it works until the 12th.|||Hi Rubes,
I have checked on the server's regional settings, the date format is
'dd/MM/yyyy'.
However, the problem still occurs.
Uskaka
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Adding to my query, I have done the data transformation test and the result
shows in format 'dd/MM/yyyy' but when inserted, the format goes 'MM/dd/yyyy'
.

Importing Data through DTS

Hi all
I have a problem when I import a text delimited data into SQL Server. This happens only with the date. My date format is dd/mm/yyyy in both the text file and regional settings in Windows. I have created a DTS package and schedule the job to run daily. The imported data in SQL Server show mm/dd/yyyy
The funny part is when I execute the package directly from DTS, the data were imported without the problem. But when I execute the job in SQL Server Agent, the problem arises. Also this happens only early of the month, from 1st - 12th
I would appreciate it if anyone can help me in resolving this problem
ps : My data type for the date column is datetime.when you execute the job in SQL Server Agent, it runs with the settings on the server.
when you execute the package directly from DTS, it runs with your local settings.
It suggests that the regional settings in Windows on the server are mm/dd/yyyy - which explains why it works until the 12th.|||Adding to my query, I have done the data transformation test and the result shows in format 'dd/MM/yyyy' but when inserted, the format goes 'MM/dd/yyyy'.

Importing a text file

I am trying to import a text file into an existing table using the DTS
wizard. I am having trouble getting it to accept a couple of date fields. Is
there a date format that the wizard will accept? If not, how should I
import date fields?
Thanks,
Tim
Hi,
Before I comment anything, can you please copy and paste the data (10 rcords
will do)
in the date column inside the text file.
Thanks
Hari
MCDBA
"Tim" <vbopen@.yahoo.com> wrote in message
news:eAxBov0VEHA.1012@.TK2MSFTNGP09.phx.gbl...
> I am trying to import a text file into an existing table using the DTS
> wizard. I am having trouble getting it to accept a couple of date fields.
Is
> there a date format that the wizard will accept? If not, how should I
> import date fields?
> Thanks,
> --
> Tim
>
|||Hari,
Thanks for the reply.
The problem was that there were some records that did not have a date and
just had " / / ". I edited the file and replaced them with a dummy date and
it imported fine.
Tim
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eCEZQ$1VEHA.2928@.tk2msftngp13.phx.gbl...
> Hi,
> Before I comment anything, can you please copy and paste the data (10
rcords[vbcol=seagreen]
> will do)
> in the date column inside the text file.
> --
> Thanks
> Hari
> MCDBA
> "Tim" <vbopen@.yahoo.com> wrote in message
> news:eAxBov0VEHA.1012@.TK2MSFTNGP09.phx.gbl...
fields.
> Is
>