• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 700
  • Last Modified:

Inserting a 000000 date value in a field

Hello,

I have an ascii file that i am inserting the records into a MS SQL (2008) table.  The Birthdate field is sometimes a valid date in mmddyy format which I am using Substring to put it into yymmdd format for the field in SQL.  The problem is when the date field in the ascii file is all zeros 000000 (mmddyy) Insert script will not run, I get an error saying "Conversion failed when converting date adn/or tiem from character string."  How can I get the ascii date value of 000000 accepted into the SQL date field?  The field in SQL is a date field and allows nulls?

Thanks,
0
JohnJMA
Asked:
JohnJMA
1 Solution
 
rushShahCommented:
you can use case when like this,

insert into tablename
select case when date='000000' then null else date end as date
from tablename
0
 
JohnJMAAuthor Commented:
Thanks, it works great.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now