How to insert null as a value for the sql table DateTime column from excel?

Hi All,

i am able to insert the values from excel sheet columns data to the corresponding columns of an sql table.

But the problem is

in my execl sheet i have some date columns.In those some rows are having data(date) and some rows are empty.

Now when i am sending these data to a datetime column of sql table,the rows which are having dates are showing as it is.But the rows which are empty are showing with a default date(1900-01-01 00:00:00.000) in the sql table.

But i need Null instead of 1900-01-01 00:00:00.000.

If i check by inserting the null values from sql.I am getting null values to the date columns.

Note:I didn't use notnull constraint.

Please lt me know how can i achieve like this?

Thanks in advance

Regards
Kumar

What database are you using and how are toy importing the data?

Hi Kyle,

I am using Sql server as a database.

and I am importing the data from excel macro by using insert query in that.

Regards
Kumar

Can't you just use an insert query and leave out that particular column?

Kyle,

No, I didn't use like that.

I am using only one Insert query.

Before this i'm checking whether there empty data or not.

if it is empty i'm using Null or "" (these are 2 cases)
if not the existing data.

Regards
Kumar

Then I think you're going to have to post your code since I've no idea how you're doing it.

If you run an insert query, specify your columns, but leave one out then SQL server will default the value to Null

Kyle,

This is the code that i am using.

Please look into it.

Please Login or Register  to view this content.

Regards
Kumar

You aren't specifying your columns in your INSERT query, just have 2 sql statements, one INSERT as you have and another with columns specified leaving out the column that could be null. SQLS will then put a null in by default

Kyle,

Sorry i didn't get you exactly.
Please can you help me a bit clear.

Regards
Kumar

Sure, use this format:

PHP Code: 

INSERT INTO table_name 
            (column1, 
             column2, 
             column3) 
VALUES      (value1, 
             value2, 
             value3) 


Rather than:

PHP Code: 

INSERT INTO table_name 
VALUES      (value1, 
             value2, 
             value3) 


That way, you can remove one of the fields and not pass a value, that will then insert null

Kyle,

Thanks for your reply.
I have changed my sql query as you suggested.But i didn't get the result how i want for the empty values of date columns.

see this is the changed query.

Please Login or Register  to view this content.

Please let me know if i do anything wrong.

Regards
Kumar

Of course you haven't you are missing the point. If you do not want to include a field in an insert remove if from the sql, so if you didn't want month and year:

PHP Code: 

sSQL = "INSERT INTO TblLabParams([Protocol],[ProjectName],[ProjectCoordinator],[Lot],[DateRecord],[DateReadyToMeasure],[DateMeasured],[QtyOfLensMeasured],[BC],[CT],[Power],[Diameter],[LensCrossSections],[QtyOfLensXSectioned],[Others],[CompletionDate],[TurnAroundTime]) " & _
            " VALUES (" & _
            "'" & ColA & "', " & _
            "'" & ColB & "', " & _
            "'" & ColC & "', " & _
            "'" & ColD & "', " & _
            "'" & ColE & "', " & _
            "'" & ColF & "', " & _
            "'" & ColG & "', " & _
            "'" & ColH & "', " & _
            "'" & ColI & "', " & _
            "'" & ColJ & "', " & _
            "'" & ColK & "', " & _
            "'" & ColL & "', " & _
            "'" & ColM & "', " & _
            "'" & ColN & "', " & _
            "'" & ColO & "', " & _
            "'" & ColP & "', " & _
            "'" & ColQ & "')" 


Although why you're inserting all your values as strings is totally beyond me, surely things like Diameter should be a number, not a string

Kyle,

How can i do statically like that by removing Date fields and their corresponding values from the query.
Because i need to insert the value if there is a value(date) if not only it will insert the null value in the database.
If i remove the fields how can i insert the data if there is data.

and you are asking me why i am inserting these values as strings.
because,I found the syntax in some where(exactly i don't know the syntax here to insert.)

Please correct me if i am not understanding your point.

Regards
Kumar

Then for each row you need to check to see what fields have values and build the query dynamically.

You shouldn't be inserting non strings as strings, have you set up the datatypes correctly in the database?

Have you tried using the word Null in your sql string where you want to pass a null value?

Kyle,

Ya i have setup my database correctly.I am using Datetime for date columns and all others are nvarchar as datatypes.
and i have also checked by programatically giving the value as Null by checking the cell value if empty.But no use.

But if we remove statically the columns and their values.I am getting Null in the database.But this is not the required case.

Is there any way to solve my problem?

Regards
Kumar

Well they shouldn't be nvarchar. Surely Diameter should be a number?

I don't think you have passed null correctly since you are trying to pass all your parameters as strings. Null is not a string

Kyle,

Right now i have 4 date columns.If i need to generate the query dynamically then i need to write 16 probabilities.I am doing that and it is working too.
But it is a lengthy process.

Is there any optimization for this?
If any solution at you please share with me.

Regards
Kumar

Have you read my post?

Have you tried passing null in the sql?

Kyle,

I didn't read your previous post.

This is how i am passing Null value.But still not get the result.

Please Login or Register  to view this content.

Regards
Kumar

You are still passing it as a string. Stop passing it as a string and it will work.

Wrapping ' ' round anything in a sql statement passes it as a string

Kyle,

Can you please change my query how to pass the value.
ColE,ColF,ColG,ColP are the variables in which i am storing the date column each row value.Similarly others.

I have tried but i am getting syntax errors when debugging.

Please Login or Register  to view this content.

regards
Kumar

It's quite simple, I think you think it's more complicated than it is.

If there is no value in the cell, don't pass the quotation marks in your sql string for that field, just pass "Null", not "'Null'"

Instead of putting them in the sql statement itself, add any quotation marks to the variables if required, then all you need to include are commas between the variables.

---------- Post added at 02:05 PM ---------- Previous post was at 01:59 PM ----------

Does this help?

Please Login or Register  to view this content.

Kyle,

Thanks a lot for your precious time on this post.
Yes now i understand and implemented and get the correct result.

Regards
Kumar